Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
Using xl-2007
I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
=sumproduct(--(A1:A100="t"),--(B1:B100="t"),--(or((C1:C100="x"),(D1:D100="y"))))
"Bassman62" wrote: Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
=sumproduct(--(A1:A100="t"),--(B1:B100="t"),--(or((C1:C100="x"),(D1:D100="y"))))
"Bassman62" wrote: Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
Bassman,
=SUMPRODUCT((A1:A10="t")*(B1:B10="t")*((C1:C10="x" )+(C1:C10="y"))*D1:D10) HTH, Bernie MS Excel MVP "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
To sum D when both A and B equal 't' and C is either "x" or "y"
=SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )*+(C1:C10="y"))*(D1*D10)) Think of a test (is A1= "t") as giving an answer TRUE or FALSE but Excel will treat as these 1 or 0 when we do math on them. A test B test product of A*B tests 1 1 1 0 1 0 1 0 0 0 0 0 So multiplication is the same as AND C = x test C = y test addition of two tests 1 1 1 0 1 1 1 0 1 0 0 0 So addition is the same as OR To count rather than sum; =SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )+(C1:C10="y"))) -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
Try these...
For the count: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y")) For the sum: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y"),D1:D10) Better to use cells to hold the criteria: F1 = T F2 = X F3 = Y =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3)) =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3),D1:D10) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
Bernard and Bernie,
Thank you very much. This answers my question. I now see how adding the arrays (C...="x")+(C...="y") will return 1 when C...= "x" OR "y". Thanks again. "Bernard Liengme" wrote in message ... To sum D when both A and B equal 't' and C is either "x" or "y" =SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )*+(C1:C10="y"))*(D1*D10)) Think of a test (is A1= "t") as giving an answer TRUE or FALSE but Excel will treat as these 1 or 0 when we do math on them. A test B test product of A*B tests 1 1 1 0 1 0 1 0 0 0 0 0 So multiplication is the same as AND C = x test C = y test addition of two tests 1 1 1 0 1 1 1 0 1 0 0 0 So addition is the same as OR To count rather than sum; =SUMPRODUCT((A1:A20="t")*(B1:B20="t")*((C1:C10="x" )+(C1:C10="y"))) -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
Biff,
Thanks very much for the prompt reply.(My own attempt was getting quite long.) Thanks again. "T. Valko" wrote in message ... Try these... For the count: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y")) For the sum: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y"),D1:D10) Better to use cells to hold the criteria: F1 = T F2 = X F3 = Y =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3)) =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3),D1:D10) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to write an Or statement inside Sumproduct?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Biff, Thanks very much for the prompt reply.(My own attempt was getting quite long.) Thanks again. "T. Valko" wrote in message ... Try these... For the count: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y")) For the sum: =SUMPRODUCT(--(A1:A10="T"),--(B1:B10="T"),(C1:C10="X")+(C1:C10="Y"),D1:D10) Better to use cells to hold the criteria: F1 = T F2 = X F3 = Y =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3)) =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=F1),(C1:C10=F2)+(C1:C10=F3),D1:D10) -- Biff Microsoft Excel MVP "Bassman62" wrote in message ... Using xl-2007 I reallize that the OR statement cannot be used as an array in a Sumproduct. In this case how can I sum the values in column D or count the rows where columns A & B = t and column C = x Or y? A B C D 1 t t x 10 2 t f x 5 3 t t y 10 Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect(NamedRange) inside Sumproduct | Excel Worksheet Functions | |||
how can i write complex numbers inside excel cels? | Excel Discussion (Misc queries) | |||
vlookup inside an if statement? | Excel Discussion (Misc queries) | |||
vlookup inside an if statement? | Excel Worksheet Functions | |||
Date Format Inside of a SUMIF Statement | Excel Worksheet Functions |