CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language
marksus1
marksus1

Reputation: 79

Get SUM from CATEGORY and by MONTH

So I'm trying to sort some data from a form in google sheets. I need to sort the data by category. and by the month. So far I have been able to sort these individually but not in the same cell.

my code so far for my test form is below the image.

enter image description here

To Filter by a category, in F2: =UNIQUE(B2:B25)

for cells G2 and below I used: =SUMIF(B$2:B$25,F2,C$2:C$25)

to get the TOTAL for entire categories I used: =SUMPRODUCT((MONTH(spending_response!D2:D100)=5)*(YEAR(spending_response!D2:D100)=2020)*(spending_response!C2:C100))

my problem is I can't put these two together. I tried adding the two codes in the same cell separate by a comma but it doesn't seem to work. please see the below image for what I am using this for and dismiss the test values.

enter image description here

Upvotes: 2

Views: 211

Answers (2)

player0
player0

Reputation: 1

use:

=QUERY(A2:C, "select A,sum(B) where month(C)+1=5 group by A label sum(B)''", 0)

0

Upvotes: 1

p._phidot_
p._phidot_

Reputation: 1952

Put this in G2 and drag downwards : 

=SUMPRODUCT((spending_response!$B$2:$B$100=F2)*(MONTH(spending_response!$D$2:$D$100)=5)*(YEAR(spending_response!$D$2:$D$100)=2020)*(spending_response!$C$2:$C$100))

Idea : add another draggable 'checking' criteria to the sumproduct. /(^_^)

Upvotes: 2

Related Questions