sum value from a date range for a specific product

问题:

I am looking to sum up multiple values from multiple cells where they will match a date and a product.

like below: I have a date set in every cell in row 2 as below, and I have my categories in column A

now I want to pick the total value (that is unit costs) from (below picture) this sheet where the date is equal to 1-dec (date) and the category is equal to office.

I tried using this formula ( =sum(query(DataSheet!A3:J10, "select a,c,i where a= '"&C2&"' and c= '"&A4&"'",0),0) ) - but it is showing me a value error.

please help me to solve this issue.

回答1:

first you will need to fix those dates:

try:

then:

=SUM(IFERROR(QUERY(DataSheet!$A3:$J4, 
 "select J 
  where C = '"&$A4&"' 
    and A = date '"&TEXT(C$2, "yyyy-mm-dd")&"'", 0)))