Sample workbook for this problem can be found here
https://docs.google.com/spreadsheets/d/1IkNv6outjKzwgvnG_7IPLpSt2vEAtcrq/edit?usp=share_link&ouid=117355411294900898171&rtpof=true&sd=true
I have a table in excel that looks like this.
What I want, is to create this table from the data.
If that’s all I needed, then I could just use a pivot table, but for reasons unimportant here, that’s not a possible solution.
I can use SUMIF(Category, "Car", Amount) to get the data for say all of cars, but this doesn’t work with two variables.
Thus, I go to SUMIFS(Table1[Amount], YEAR(Table1[Date]), 2021, Table1[Category], "Car"), but this throws a "problem with this formula error" and won’t accept it. Btw, this doesn’t work whether I use a cell reference or write in the criteria information.
Maybe there is an answer using SUMPRODUCT or arrays or something, but it’s beyond me.
I found a solution that looks like this
=SUMIFS(Table1[Amount], Table1[Category], "car", Table1[Date], ">=01/01/"&C22, Table1[Date], "<=12/31/"&C22). Where C22 is the year "2022" or whatever and "car" could be replaced by a cell reference easily enough. This works, but doesn’t feel elegant. Is there a better way?
>Solution :
As you said you can use SUMPRODUCT:
=SUMPRODUCT(Table1[Amount], (YEAR(Table1[Date])=2021)*(Table1[Category]="Car"))
Note with office 365 SUMPRODUCT can be just SUM
or use the SUMIFS as you show.
=SUMIFS(Table1[Amount], Table1[Category], "car", Table1[Date], ">=01/01/"&C22, Table1[Date], "<=12/31/"&C22)
You could also use (with office 365):
=SUM(FILTER(Table1[Amount], (YEAR(Table1[Date])=2021)*(Table1[Category]="Car")))
But that is just a variation of the SUMPRODUCT.