Sum column in google excel using 2 conditions

0
votes

I have a table with 4 columns in a google spreadsheet [timestamp, item, amount, category] and would like to get all rows that belong to a specific month and a specific category and sum the amount column.

So far I have tried

=QUERY(Sheet1!A:D,"SELECT SUM(C) WHERE MONTH(A) = 1 AND D = 'some text' GROUP BY A",1)

and

=IF(AND(EQ(Sheet1!D2:D,B17),MONTH(Sheet1!A2:A)=1),SUM(Sheet1!C2:C))

Where B17 is a text that I want a comparison to be made.

2
google-sheetsgoogle-sheets-formulagoogle-sheets-query

2 Answers

1
votes

In Google spreadsheets, try

=sumproduct(Sheet1!D2:D=B17,MONTH(Sheet1!A2:A)=1, Sheet1!C2:C)

and see if that works?

If you'd prefer query, you can try

=QUERY(Sheet1!A:D,"SELECT SUM(C) WHERE MONTH(A) = 0 AND D = '"&B17&"' label SUM(C) ''",1)

Note that MONTH() in query is zero-indexed: so January would be month 0.

0
votes

You can change this one:

=IF(AND(EQ(Sheet1!D2:D,B17),MONTH(Sheet1!A2:A)=1),SUM(Sheet1!C2:C))

with 

=sum(filter(Sheet1!C2:C,(Sheet1!D2:D=B17)*(MONTH(Sheet1!A2:A)=1)))