Google sheets use a filter on date and category in one go

Question

I am trying to sum up values in google sheets. I want a monthly overview and a sum per category.

My data has these characteristics:

 date, category, value
 01-01-2021, blue, 50
 02-01-2021, green, 100
 01-02-2021, blue 50
 15-01-2021, orange, 30
 12-02-2021, orange, 50
 18-01-2021, blue, 20

I want to group it as follows:

 month, category, value
 January, blue, 70
 January, orange, 30
 February, blue, 50
 February, green, 100
 February, orange, 50

I managed to sum values that are between certain dates. So combining the date and value column as in above example. Now I would like to add the category to it.

My function:

=SUMIFS(F2:F362;B2:B362;">="&D3;B2:B362 ;"<="&D4)

F2:F362 is the range of the values B2:B362 is the range of the dates D3 = Minimal date D4 = maximum date

Any help or tips would be appreciated! Not sure if I should continue in this function or add something around it. Couldn't find this combined challenge in current questions, only the separate ones.

Answer 1

try:

=QUERY(A1:C, 
 "select month(A)+1,sum(C) 
  where A is not null 
  group by month(A)+1 
  pivot B
  label month(A)+1'month'", 1)

---

or the reverse distribution:

=QUERY(A1:C, 
 "select B,sum(C) 
  where A is not null 
  group by B 
  pivot month(A)+1", 1)

---

to add external date boundaries:

=QUERY(A1:C, 
 "select B,sum(C) 
  where A >= date '"&TEXT(D3, "yyy-mm-dd")&"'
    and A <= date '"&TEXT(D4, "yyy-mm-dd")&"'
  group by B 
  pivot month(A)+1", 1)