SUMIF with two conditions (or)
 

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?

SUMIF with two conditions (or)
 

Try something like this:

=SUMPRODUCT((((A2:A24="01")+(B2:B24="01"))0)*C2:C 24)

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

Regards,

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

wrote in message
oups.com...
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?

SUMIF with two conditions (or)
 

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

On Nov 11, 1:04 am, wrote:
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?

SUMIF with two conditions (or)
 

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" wrote in message
ups.com...
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

On Nov 11, 1:04 am, wrote:
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?

SUMIF with two conditions (or)
 

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

Pete

On Nov 11, 1:39 am, "Ron Coderre"
wrote:
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" wrote in message

ups.com...



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

On Nov 11, 1:04 am, wrote:
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?- Hide quoted text -

- Show quoted text -

SUMIF with two conditions (or)
 

Simplify version:

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


"Pete_UK" wrote:

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

On Nov 11, 1:04 am, wrote:
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?

SUMIF with two conditions (or)
 

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)


"Teethless mama" wrote in message
...
Simplify version:

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


"Pete_UK" wrote:

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

On Nov 11, 1:04 am, wrote:
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?

SUMIF with two conditions (or)
 

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:C 24)

I don't think it double counts..