Sumifs+sumifs+sumifs

[AnAppleADay]

I'm trying to avoid stringing together multiple SUMIFS to find the total value of a cell, where two lots of criteria are matched. I have done some research and keep getting sent off an jaunty anglestowards the land of SUMPRODUCT and INDEX/MATCH - Which i've tried but it seems like I'm going backwards so i've come back to my original formula (which works - but is long!):

=SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$5,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$6,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$7,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$8,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$9,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$10,Sheet1!$T$2:$T$100000,B$2)+SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$11,Sheet1!$T$2:$T$100000,B$2)


Argument 1: Column U - This is where my values are
Argument 2: Column A - This is the first criteria range
Argument 3: Pack Sizes - The pack sizes which i want to add as an array are here ($A$5-$A$11 - Currently this only works if i list the SUMIFS separately)
Argument 4: Column T: My second criteria is a Week number, this data is in Column T.
Argument 5: B$2 - This correspondences to be certain week number (ie. B$2 = Week 6. C$2 = Week 7... etc)

I am using a Mac - I'm not sure what difference it makes (except how to apply an array formula) - Which i've tried, but it only picked up the first value in the array rather than returning the sum of the value of the whole array + my 2nd criteria.

The array formula i tried was:
=SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$5,Sheet1!$T$2:$T$100000,B$2)

 

[Jonmo1]

Welcome to the board.

Try

=SUMPRODUCT(SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$A$2:$A$100000,'Pack Sizes'!$A$5:$A$11,Sheet1!$T$2:$T$100000,B$2))

 

[Jonmo1]

(which works - but is long!)

That isn't necessarily a bad thing.
I wouldn't put 'length of formula' high on my list of criteria when judging the quality of a formula.
A couple rules of thumb come to mind.
Shorter does not necessarily mean better
If it ain't broke, don't fix it.

That said, the sumproduct(sumifs()) isn't necessarily 'better' than your original. It is basically just a shortcut to sumifs+sumifs.
The same amount of calculations still happens, it still calculates the result of each sumif, then adds them together.

If you're actually looking for a 'better' solution, I would suggest a helper column on Sheet1, in say column B for example.
In B2 and filled down to B100000
=COUNTIF('Pack Sizes'!$A$5:$A$11,$A2)

Then use
=SUMIFS(Sheet1!$U$2:$U$100000,Sheet1!$B$2:$B$100000,">0",Sheet1!$T$2:$T$100000,B$2)