| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIF based on Multiple Conditions
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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumIF based on Multiple Conditions
Remove the quotes from around the 11:
....(D$2:D$9999="11")... ....(D$2:D$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$999 9=I2)*(D$2:D$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:D$9999=J2),E$2:E$9999) -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumIF based on Multiple Conditions
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. -- Karthi "T. Valko" wrote: Remove the quotes from around the 11: ....(D$2:D$9999="11")... ....(D$2:D$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$999 9=I2)*(D$2:D$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:D$9999=J2),E$2:E$9999) -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumIF based on Multiple Conditions
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. -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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. -- Karthi "T. Valko" wrote: Remove the quotes from around the 11: ....(D$2:D$9999="11")... ....(D$2:D$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$999 9=I2)*(D$2:D$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:D$9999=J2),E$2:E$9999) -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumIF based on Multiple Conditions
Thank you very much T. Valko,
It's doing great, you've reduced my troubles... -- Karthi "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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. -- Karthi "T. Valko" wrote: Remove the quotes from around the 11: ....(D$2:D$9999="11")... ....(D$2:D$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$999 9=I2)*(D$2:D$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:D$9999=J2),E$2:E$9999) -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumIF based on Multiple Conditions
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Karthik" wrote in message ... Thank you very much T. Valko, It's doing great, you've reduced my troubles... -- Karthi "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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. -- Karthi "T. Valko" wrote: Remove the quotes from around the 11: ....(D$2:D$9999="11")... ....(D$2:D$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$999 9=I2)*(D$2:D$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:D$9999=J2),E$2:E$9999) -- Biff Microsoft Excel MVP "Karthik" wrote in message ... 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:D$9999="11"),E$2:E$9999))} A B C D E xx xy yy 11 123 -- Karthi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SumIF-multiple conditions/OR | New Users to Excel | |||
| SUMIF with Multiple Conditions | Excel Worksheet Functions | |||
| sumif with multiple conditions | Excel Worksheet Functions | |||
| SUMIF based on two conditions | Excel Worksheet Functions | |||
| SUMIF based on 2 conditions | Excel Worksheet Functions | |||
Hybrid Mode
