SOLVED

Several SUMIFs stringed together - How to shorten?

%3CLINGO-SUB%20id%3D%22lingo-sub-1506982%22%20slang%3D%22en-US%22%3ESeveral%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1506982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20won't%20go%20into%20too%20much%20detail%20of%20what%20the%20information%20means%20but%20I%20have%20a%20really%20long%20bunch%20of%20SUMIFS%20formulas%20which%20are%20stringed%20together.%20It%20works%20as%20intended%2C%20but%20I'd%20like%20to%20try%20and%20shorten%20it%20down%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B6%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Mornings%22)%2BSUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B6%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Afternoons%22)%2BSUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B6%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Seniors%22)%2BSUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B6%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Overnights%22)%2BSUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B3%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Weekends%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20first%20three%20SUMIFS%20statements%20the%20criteria%20is%20exactly%20the%20same%20except%20it%20says%26nbsp%3B%20'EU%20-%20Mornings'%2C%20'EU%20-%20Afternoons%2C%20'EU%20-%20Overnights'%20and%20'EU%20-%20Seniors'.%20With%20these%20SUMIFS%20I%20want%20to%20return%20anything%20that%20is%20%26lt%3B6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20in%20the%20SUMIFs%2C%20I%20have%20a%20different%20criteria%20which%20is%20under%20the%20'EU%20-%20Weekends'%20and%20this%20time%20the%20criteria%20is%20%26lt%3B3.%20I%20want%20the%20total%20to%20reflect%20the%20fact%20there%20is%20a%20variable%20in%20the%20criteria%20when%20it%20comes%20to%20'EU%20-%20Weekends'%20being%20less%20than%203%2C%20versus%20the%20other%20categories%20being%20less%20than%206.%20I'm%20sure%20there%20is%20a%20way%20to%20make%20this%20more%20efficient%20but%20my%20Excel%20skills%20are%20minimal.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%E2%80%8B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1506982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507022%22%20slang%3D%22en-US%22%3ERe%3A%20Several%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721527%22%20target%3D%22_blank%22%3E%40robrymond%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20in%20mind%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(SUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B6%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C%0A%20%20%20%20%20%20%20%20%20'2020-21'!%24E%244%3A%24E%248%2C%7B%22EU%20-%20Mornings%22%2C%22EU%20-%20Afternoons%22%2C%22EU%20-%20Seniors%22%2C%22EU%20-%20Overnights%22%7D))%2B%0A%20SUMIFS('2020-21'!%24NQ%244%3A%24NQ%248%2C'2020-21'!%24NR%244%3A%24NR%248%2C%22%26lt%3B3%22%2C'2020-21'!%24D%244%3A%24D%248%2C%22EU%22%2C'2020-21'!%24G%244%3A%24G%248%2C%22No%22%2C'2020-21'!%24E%244%3A%24E%248%2C%22EU%20-%20Weekends%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507081%22%20slang%3D%22en-US%22%3ERe%3A%20Several%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507081%22%20slang%3D%22en-US%22%3E%3CP%3ELooks%20like%20thats%20solved%20it%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507149%22%20slang%3D%22en-US%22%3ERe%3A%20Several%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESlightly%20shorter%20if%20you%20also%20introduce%20the%20array%20criteria%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22%26lt%3B%22%26amp%3B%7B6%2C6%2C6%2C6%2C3%7D%3C%2FSTRONG%3E%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507246%22%20slang%3D%22en-US%22%3ERe%3A%20Several%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%2C%20but%20I%20didn't%20dare%20to%20suggest%20without%20testing%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507295%22%20slang%3D%22en-US%22%3ERe%3A%20Several%20SUMIFs%20stringed%20together%20-%20How%20to%20shorten%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20test%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20SUM(%0A%20%20SUMIFS(%0A%20%20%20%20X%2C%0A%20%20%20%20Y%2C%22%26lt%3B%22%26amp%3B%7B6%3B6%3B6%3B6%3B3%7D%2C%0A%20%20%20%20D%2C%22EU%22%2C%0A%20%20%20%20G%2C%22No%22%2C%0A%20%20%20%20E%2C%22EU%20-%20%22%26amp%3B%7B%22Mornings%22%3B%22Afternoons%22%3B%22Seniors%22%3B%22Overnights%22%3B%22Weekends%22%7D)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20concluded%20that%20it%20simply%20replicated%20your%20solution.%26nbsp%3B%20Also%20my%20defined%20names%20were%20somewhat%20cryptic.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by robrymond (New 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")

 

Highlighted

Looks like thats solved it, thanks!

@Sergei Baklan 

Slightly shorter if you also introduce the array criteria

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

Highlighted

@Peter Bartholomew 

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

Highlighted

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