Sumif multiple criteria are met

tammymaxson

New Member
Joined
Sep 8, 2006
Messages
4
I wnat to create a summary sheet that looks at the master sheet and sums the data in column E only if it meets two or three other sets of cirteria:

For example: What formula would I enter to bring me the result of $200.00

I want a sum of the related data in column H if:

column d= "Problem"
and
Column I = "270"
and
Column H = "Pending"


Column D Column E Column H Column I
Problem 100.00 pending 270
Problem 100.00 not In Pending 270
Ready 100.00 pending 270
Problem 100.00 pending 180
Ready 100.00 not In Pending 270
Problem 100.00 pending 270

Currently I create a pivot table for this data print it out and the reenter the data manully into a differnt spreadsheet format. I tried just linking my fileds to the pivot table, but the minute on of the categories is not there in the pivot table the next month (like Ready) than the formula does not work, so I thought I could eliminate the pivot table step and use a sumif right in this field instead.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
=SUMPRODUCT(--(D2:D100="Problem"),--(H2:H100="Pending"),--(I2:I100=270),E2:E100)

Note that you cannot use whole columns (D:D, etc) in SUMPRODUCT.
 
Upvote 0
I tried it changing you -- to Master! which is the name of the worksheet that contains the data and it does not work.

=SUMPRODUCT(Master!(D2:D100="Problem"),Master!(H2:H100="Pending"),Master!(I2:I100=270),E2:E100)

Any suggestions?
 
Upvote 0
Try...

=SUMPRODUCT(--(Master!D2:D100="Problem"),--(Master!H2:H100="Pending"),--(Master!I2:I100=270),Master!E2:E100)

Hope this helps!
 
Upvote 0
I had an same kind of a problem and this solution worked. But it is awfully slow, is there any way to make it faster or otherwise fasten it up? I only need to check two criteria instead of three does it make any difference.

Thank you in advance.
 
Upvote 0
I had an same kind of a problem and this solution worked. But it is awfully slow, is there any way to make it faster or otherwise fasten it up? I only need to check two criteria instead of three does it make any difference.

Thank you in advance.

Concatenate the two columns where the condition needs to be met, then use SUMIF for the new column. So, for example, if the criteria needs to be met for Column A and Column B, concatenate the two columns, let's say in Column C...

C1, copied down:

=A1&"#"&B1

Then use SUMIF...

=SUMIF(C1:C100,E1&"#"&F1)

...where E1 contains the criteria for Column A, and F1 contains the criteria for Column B.

Hope this helps!
 
Upvote 0
Thank you very much. It shortened calculation time to fraction of what is used to be.

Now the question is. Are there any benefits for using previously recommended formula instead of this more simple one? I assume I can't get this solution to work if I need more than 2 criteria for sumif?
 
Upvote 0
You can get this solution to work for as many criteria as you like, simply concatentate the extra criteria in a similar fashion to the above.
 
Upvote 0

Forum statistics

Threads
1,218,899
Messages
6,145,093
Members
450,590
Latest member
Naneng

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
Back
Top