Sumifs for multiple vertical and horizontal criteria-CodePudding

I would have to summarize data followingly:

Criterias are: date in the vertical axis, and the unit and name in the horizontal axis:

I would have to get a sum for Customer 1, main category and for time-period of 11/2020 - 12/2021

Thanks for help!

CodePudding user response：

Assuming column B contains dates, you can use SUMIFS and INDEX/MATCH:

=SUMIFS(INDEX(C:F,,MATCH("Customer 1",$C$1:$F$1,0)),B:B,">="&DATE(2020,11,1),B:B,"<="&DATE(2021,12,31))

Adjust the columns in C:F and $C$1:$F$1 as needed.

The DATE is not necessary:

=SUMIFS(INDEX(C:F,,MATCH("Customer 1",$C$1:$F$1,0)),B:B,">=11/1/2020",B:B,"<=12/31/2021")

If you want to get "Other" for example, just add 1 to the result of MATCH:

=SUMIFS(INDEX(C:F,,MATCH("Customer 1",$C$1:$F$1,0) 1),B:B,">=11/1/2020",B:B,"<=12/31/2021")

CodePudding user response：

You may try using SUMPRODUCT Function as well

Formula used in cell J7

=SUMPRODUCT((LOOKUP(COLUMN($D$1:$H$1),COLUMN($D$1:$H$1)/
($D$1:$H$1<>""),$D$1:$H$1)=$J$3)*($J$4=$D$3:$H$3)*
($J$5<=$B$4:$B$21)*($J$6>=$B$4:$B$21)*($D$4:$H$21))