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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
=SUMPRODUCT(--(D2:D100="Problem"),--(H2:H100="Pending"),--(I2:I100=270),E2:E100)

Note that you cannot use whole columns (D:D, etc) in SUMPRODUCT.
 

tammymaxson

New Member
Joined
Sep 8, 2006
Messages
4
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?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

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

Hope this helps!
 

tammymaxson

New Member
Joined
Sep 8, 2006
Messages
4

ADVERTISEMENT

Your the best THANKS!!!!!
 

thepanu

New Member
Joined
Sep 30, 2004
Messages
40
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406

ADVERTISEMENT

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!
 

thepanu

New Member
Joined
Sep 30, 2004
Messages
40
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?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

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