SOLVED

New Contributor

# Several SUMIFs stringed together - How to shorten?

I won't go into too much detail of what the information means but I have a really long bunch of SUMIFS formulas which are stringed together. It works as intended, but I'd like to try and shorten it down:

``=SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<6",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Mornings")+SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<6",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Afternoons")+SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<6",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Seniors")+SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<6",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Overnights")+SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<3",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Weekends")``

For the first three SUMIFS statements the criteria is exactly the same except it says  'EU - Mornings', 'EU - Afternoons, 'EU - Overnights' and 'EU - Seniors'. With these SUMIFS I want to return anything that is <6.

Also in the SUMIFs, I have a different criteria which is under the 'EU - Weekends' and this time the criteria is <3. I want the total to reflect the fact there is a variable in the criteria when it comes to 'EU - Weekends' being less than 3, versus the other categories being less than 6. I'm sure there is a way to make this more efficient but my Excel skills are minimal.

5 Replies
best response confirmed by robrymond (New Contributor)
Solution

# Re: Several SUMIFs stringed together - How to shorten?

First in mind

``````=SUM(SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<6",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",
'2020-21'!\$E\$4:\$E\$8,{"EU - Mornings","EU - Afternoons","EU - Seniors","EU - Overnights"}))+
SUMIFS('2020-21'!\$NQ\$4:\$NQ\$8,'2020-21'!\$NR\$4:\$NR\$8,"<3",'2020-21'!\$D\$4:\$D\$8,"EU",'2020-21'!\$G\$4:\$G\$8,"No",'2020-21'!\$E\$4:\$E\$8,"EU - Weekends")``````

# Re: Several SUMIFs stringed together - How to shorten?

Looks like thats solved it, thanks!

# Re: Several SUMIFs stringed together - How to shorten?

Slightly shorter if you also introduce the array criteria

"<"&{6,6,6,6,3} ?

# Re: Several SUMIFs stringed together - How to shorten?

Perhaps, but I didn't dare to suggest without testing

# Re: Several SUMIFs stringed together - How to shorten?

I did test

``````= SUM(
SUMIFS(
X,
Y,"<"&{6;6;6;6;3},
D,"EU",
G,"No",
E,"EU - "&{"Mornings";"Afternoons";"Seniors";"Overnights";"Weekends"})
)``````

but concluded that it simply replicated your solution.  Also my defined names were somewhat cryptic.