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?

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?

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)

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)