SUMIF with two conditions (or)

[urselhempel]

Hello, so far I haven't found a discussion about my specific problem.

I have a worksheet with different patient samples. Two columns (A and
B) specify the HLA alleles of the patient (lets say 01, 02, 03, 04,
etc.), another column (C) specifies the magnitude of a serum marker
(for example 340, 4350 or 1230).

I now want to add up the serum marker quantities if the patient is
lets say HLA 01, that means if column 1 OR column two say "01".

I put in a function like this

=SUMIF(A2:B24,"01",C2:C24)

but it doesn't bring back correct results. SUMPRODUCT obviously doesnt
work because I want the OR logic, so a Patient with the HLA type 01/02
would be counted just like a patient with 01/01. Understandable?

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT((((A2:A24="01")+(B2:B24="01"))>0)*C2:C24)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Pete_UK]

Try this:

=SUMPRODUCT((A2:A24="01")+(B2:B24="01"),C2:C24)

or you could put the 01 in a cell (D2?) and make it:

=SUMPRODUCT((A2:A24=D2)+(B2:B24=D2),C2:C24)

Hope this helps.

Pete

 

[Ron Coderre]

Not sure if this is a possible scenario, but if there
are two "01"'s in the same row, the formula double counts.

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Pete_UK]

Thanks for pointing that out, Ron. The OP does have an example of 01
in two columns.

Pete

 

[Guest]

Simplify version:

=SUMPRODUCT((A2:B24="01")*C2:C24)

 

[Ron Coderre]

Still double-counts Col_C if both Col_A and Col_B equal "01" in the same
row.


--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[urselhempel]

Thanks guys! I tried Ron's version and the one from Pete_UK and I
think Ron's works:

=SUMPRODUCT((((A2:A24="01")+(B2:B24="01"))>0)*C2:C24)

I don't think it double counts..