2

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

player0
  • 124,011
  • 12
  • 67
  • 124
marksus1
  • 79
  • 1
  • 6
  • 1
    does putting this in G2 and drag downwards work? : `=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))` – p._phidot_ Dec 16 '19 at 07:12
  • @p._phidot_ SOLVED. Thanking you kindly!! Can you post this as an answer so I can mark it as solved? thanks again – marksus1 Dec 16 '19 at 09:21

2 Answers2

2

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

p._phidot_
  • 1,913
  • 1
  • 9
  • 17
1

use:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124