# Sumifs+sumifs+sumifs

##### New Member
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)

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Jonmo1

##### MrExcel MVP
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
(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)

Replies
2
Views
160
Replies
6
Views
189
Replies
1
Views
120
Replies
9
Views
160
Replies
8
Views
294

1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

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

### Which adblocker are you using?

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

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