Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with two conditions | Excel Worksheet Functions | |||
Sumif with n conditions | Excel Worksheet Functions | |||
SUMIF with 2 conditions | Excel Worksheet Functions | |||
sumif with 2 conditions ?? can this be done?? | Excel Worksheet Functions | |||
sumif with two conditions | Excel Worksheet Functions |