SumIF based on Multiple Conditions

[Karthik]

I have data in 5 different columns, of which Column 'A', 'B' and 'C' contains
text and Column 'D' and 'E' contains numbers. Could you please tell me a
formula through which i can add the numbers in column 'E' if Column 'A', 'B',
'C' and D has the the required data.

I tried with an array formula
{=SUM(IF((A$2:A$9999="XX")*(B$2:B$9999="XY")*(C$2:C$9999="yy")*(D$2$9999="11"),E$2:E$9999))}

A B C D E
xx xy yy 11 123

 

[T. Valko]

Remove the quotes from around the 11:

....(D$2$9999="11")...

....(D$2$9999=11)...

Better to use cells to hold the criteria then reference those cells:

G2 = XX
H2 = XY
I2 = YY
J2 = 11

Array entered:

=SUM(IF((A$2:A$9999=G2)*(B$2:B$9999=H2)*(C$2:C$9999=I2)*(D$2$9999=J2),E$2:E$9999))

Or, this normally entered version:

=SUMPRODUCT(--(A$2:A$9999=G2),--(B$2:B$9999=H2),--(C$2:C$9999=I2),--(D$2$9999=J2),E$2:E$9999)

 

[Karthik]

Thank you very much T.Valko

The formula adds everything in the column 'E' but i would like to know the
formula to sum column 'E' only when the row satisfies all the conditions.

Example:
A B C D E
xx cd yy 10 10
xx cd yy 11 10
xx cd yy 10 10
xx xy yx 11 15
d yx yy 11 15

I would like to know a formula which adds column 'E' only if Col 'A'=xx, Col
'B'=cd, Col 'C'=11 and Col 'D' =10.

In the above case the anwer should be 20.

 

[T. Valko]

I would like to know a formula which adds column 'E'

only if Col 'A'=xx, Col 'B'=cd, Col 'C'=11 and Col 'D' =10.

In your posted sample there is no 11 in column C. If you meant column C =
"yy"...

The formulas I posted in my previous reply will do what you want. If they're
not working then explain what is not working. The formulas are correct.

 

[Karthik]

Thank you very much T. Valko,
It's doing great, you've reduced my troubles...

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Excel Weekend Worrior]

Note to all Nubies - Remember to hold down shift, Cntrl before Enter to allow
array formula to function