Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple conditions
Month Reason Value
1 Overcharge$2.34 1 Broken $3.12 2 Overcharge$4.50 2 Broken $3.33 4 Returned $1.34 1 Overcharge$4.50 2 Broken $2.30 1 Returned $5.20 I want to sum all the values for month 1 (January) which were a result of overcharges, broken etc. and so on for all month. That is if a condition is column A AND a condition in column B are met, all the values corresponding in column C must be added. I tried a number of variations of IF and SUMIF combinations to do this e.g. =IF(CMSO6304!$E$2:$E$804="Overcharge",SUMIF(CMSO63 04!$J$2:$J$804,"=1",CMSO6304!$D$2:$D$804),) Anybody have an idea? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple conditions
=SUMPRODUCT(--(A2:A20=1),--(B2:B20="Overcharge"),C2:C20)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Johan" wrote in message ... Month Reason Value 1 Overcharge$2.34 1 Broken $3.12 2 Overcharge$4.50 2 Broken $3.33 4 Returned $1.34 1 Overcharge$4.50 2 Broken $2.30 1 Returned $5.20 I want to sum all the values for month 1 (January) which were a result of overcharges, broken etc. and so on for all month. That is if a condition is column A AND a condition in column B are met, all the values corresponding in column C must be added. I tried a number of variations of IF and SUMIF combinations to do this e.g. =IF(CMSO6304!$E$2:$E$804="Overcharge",SUMIF(CMSO63 04!$J$2:$J$804,"=1",CMSO6304!$D$2:$D$804),) Anybody have an idea? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple conditions
Bob
what does "--" stand for in yr formula? On 23 Cze, 12:15, "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20=1),--(B2:B20="Overcharge"),C2:C20) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple conditions
It is coercing the TRUE/FALSE arrays returned by the conditional tests into
an array of 1/0s that the PRODUCT part of SUMPRODUCT can work on . More details http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jarek Kujawa" wrote in message ... Bob what does "--" stand for in yr formula? On 23 Cze, 12:15, "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20=1),--(B2:B20="Overcharge"),C2:C20) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SumIf and Multiple Conditions | Excel Discussion (Misc queries) | |||
SumIF-multiple conditions/OR | New Users to Excel | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
sumif - multiple conditions | Excel Worksheet Functions |