Simplifying SumIFs formulas for efficient excel formula

Question

The overall question is how to calculate The total amount of sales of a Cleaning business in NY and NJ over a calendar year.

I am trying to simplify a formula where you are using sumifs using the same two criteria the whole time, but summing across a range of 12 columns. I have successfully calculated what I need to by repeating the sumifs portion 24 times for each month and each criteria. There's got to be an easier way that I'm missing.

=SUMIFS($D$2:$D$28,$C$2:$C$28,"NY",$B$2:$B$28,"Cleaning")+SUMIFS($D$2:$D$28,$C$2:$C$28,"NJ",$B$2:$B$28,"Cleaning")+SUMIFS($E$2:$E$28,$C$2:$C$28,"NY",$B$2:$B$28,"Cleaning")+SUMIFS($E$2:$E$28,$C$2:$C$28,"NJ",$B$2:$B$28,"Cleaning")+SUMIFS($F$2:$F$28,$C$2:$C$28,"NY",$B$2:$B$28,"Cleaning")+SUMIFS($F$2:$F$28,$C$2:$C$28,"NJ",$B$2:$B$28,"Cleaning")    and so on and so forth.........

I have tried researching sumproduct, index matches, and different arrays to use, but I have not had any luck applying what I have read here or on Youtube to a type of problem like this, and I'm pretty stuck.

Answer 1

Use FILTER to filter the array of sales by the business and state. Note the * and + operators in the FILTER formula represent logical AND and OR.

Use SUM to sum up all the values in the filtered array.

=SUM(FILTER($C$1:$F$9,($A$1:$A$9="Cleaning")*(($B$1:$B$9="NY")+($B$1:$B$9="NJ"))))