CODEWITHSUNDEEP
google-sheetsspreadsheetformulas
Cata
Cata

Reputation: 498

How to SUM rows filtered by multiple criteria

Google Sheets question.

I have the following sheet (named "x") containing expenses:

Date        Sum Category
1/Jan/2017  100 red
2/Jan/2017  200 blue
3/Jan/2017  10  red
4/Jan/2017  20  blue
1/Feb/17    1   red
2/Feb/17    2   blue

I need to compute monthly totals, per category:

Month   Red Blue
Jan/17  110 220
Feb/17  1   2

My current solution is to place in each result cell something like:

=SUM(IFERROR(FILTER(x!$B:$B, MONTH(x!$A:$A)=MONTH($A2), x!$C:$C="red")))

I am asking if there is a better way. I want to have a single result formula working over an array (maybe an ArrayFormula?!) instead of placing and customizing my formula in each cell.

Any ideas?! Thanks!

Upvotes: 2

Views: 891

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34190

Well this is the basic idea of using pivot tables but not quite there because I can only get the month as a number so far

query(A:C,"select month(A)+1,sum(B) where C<>'' group by month(A) pivot C label month(A)+1 'Month Number'")

enter image description here

EDIT

The month number can be changed to a month name using an array formula and VLOOKUP but the formula is getting a bit long (my table of month numbers and names is in columns I & J)

=arrayformula({vlookup(query(query(A:C,"select month(A)+1,sum(B) where C<>'' group by month(A) pivot C label month(A)+1 'Month Number'"),"select Col1"),I:J,2,false)})

and you've still got to pick up the other two columns - this is the whole thing

=arrayformula({vlookup(query(query(A:C,"select month(A)+1,sum(B) where C<>'' group by month(A) pivot C label month(A)+1 'Month Number'"),"select Col1"),I:J,2,false),query(query(A:C,"select month(A)+1,sum(B) where C<>'' group by month(A) pivot C label month(A)+1 'Month Number'"),"select Col2,Col3")})

enter image description here

Upvotes: 0

Related Questions