CODEWITHSUNDEEP
excelexcel-formulasumifs
pando44
pando44

Reputation: 3

Simplifying SumIFs formulas for efficient excel formula

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.........

Screen shot of table

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.

Upvotes: 0

Views: 131

Answers (2)

P.b
P.b

Reputation: 11468

Alternatively you could declare the condition(s) using LET and use MMULT, so you do not need to repeat $B$1:$B$9=...:

=LET(c,{"NY","NJ"},
SUM(C2:F9*MMULT((B2:B9=c)*(A2:A9="Cleaning"),SEQUENCE(COUNTA(c))^0)))

Upvotes: 1

kevin
kevin

Reputation: 2157

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"))))

enter image description here

Upvotes: 2

Related Questions