Multi Condition Aggregation

**justh8me** · 01-05-2011, 10:35 AM

I am trying to get a formula that will return a value based on two criteria from a different sheet.
I have tried array's, I have tried if/and, I am at a loss. Any help would be greatly appreciated.

Column1 Column2 Column3
MOM ---- 1 ---- 10
MOM ---- 2 ---- 15
DAD ---- 3 ---- 20
SON ---- 2 ---- 25
DGHT ---- 5 ---- 30
SON ---- 9 ---- 35
DAD ---- 3 ---- 40

All of this info is pasted on a sheet labeled "1"
on my other sheet (same workbook)
I would like to sum everything in column3 that is labeled DAD in column1 that is also >2 in column2
So in this case it would give me 60

Let me know if this is not clear. Thanks in advance for any help

**DonkeyOte** · 01-05-2011, 10:39 AM

Welcome to the Board

If as implied you're using XL2007 you can use SUMIFS

Please Login or Register  to view this content.

of course the tests can be replaced with cell references

Pending other requirements you might find a Pivot Table would also work for you (see link in sig. for general intro.)

Prior to XL2007 you would be looking at using SUMPRODUCT which would not accept entire column references and is inefficient - so best to keep precedent ranges as small as possible:

Please Login or Register  to view this content.

on a final note please try to keep titles related to the specifics of the problem - I've modified for you on this occasion

thanks

**justh8me** · 01-05-2011, 11:11 AM

Thank you Donkey, and sorry about the subject.
That worked great. One other question, what if I want it to add on 2 or 3 separate names, Say in column1
there were DAD, DAD1, and DAD2, and I want it to add everything that is DAD, DAD1, and DAD2.
Would I use some type of "or" statement, or would I have to do seperate if's in the same line?
Again thanks for the help

**DonkeyOte** · 01-05-2011, 11:21 AM

Depends partly on the variety of items...

If there is nothing DAD* related that is not valid for aggregation then we might get away with wildcards within the SUMIFS:

Please Login or Register  to view this content.

If, however, you have items like DADO which are to be excluded then you can use something like

Please Login or Register  to view this content.

if your criteria (eg DAD,DAD1 etc) are "variables" (to be linked to cells) the above construct would not work given the inline array { } ... post back if nec.