Jul 07 2020 12:44 PM
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.
Jul 07 2020 01:02 PM
SolutionFirst 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")
Jul 07 2020 01:19 PM - edited Jul 07 2020 01:24 PM
Looks like thats solved it, thanks!
Jul 07 2020 02:03 PM
Jul 07 2020 02:47 PM
Perhaps, but I didn't dare to suggest without testing
Jul 07 2020 03:13 PM
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.
Jul 07 2020 01:02 PM
SolutionFirst 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")