Sumif multiple criteria are met

[tammymaxson]

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.

 

[Oaktree]

=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]

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]

Try...

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

Hope this helps!

 

[tammymaxson]

Your the best THANKS!!!!!

 

[thepanu]

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]

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]

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]

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.