Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

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

Solution

  • 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. /(^_^)