SOLVED

Several SUMIFs stringed together - How to shorten?

Copper Contributor

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 (Copper Contributor)
Solution

@robrymond 

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

 

Looks like thats solved it, thanks!

@Sergei Baklan 

Slightly shorter if you also introduce the array criteria

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

@Peter Bartholomew 

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

@Sergei Baklan 

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.

1 best response

Accepted Solutions
best response confirmed by robrymond (Copper Contributor)
Solution

@robrymond 

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

 

View solution in original post