Sumifs+sumifs+sumifs

AnAppleADay

New Member
Joined
Jun 11, 2018
Messages
9
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)
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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))
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
(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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,777
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top