Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
The following formula works on a column range (A1:A40), but not on a row
range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
You need to transpose your array:
=SUMPRODUCT(--(A10:U10={"P";"B";"S";"V";"H";"PBSVH"})) Note the semicolons instead of commas.... HTH, Bernie MS Excel MVP "PFB" wrote in message ... The following formula works on a column range (A1:A40), but not on a row range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
You have to make the array constant a vertical array. Using commas makes it
a horizontal array. Replace the commas with semicolons: =SUMPRODUCT(--(A10:U10={"P";"B";"S";"V";"H";"PBSVH"})) -- Biff Microsoft Excel MVP "PFB" wrote in message ... The following formula works on a column range (A1:A40), but not on a row range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
Thanks! Where can I find info on why a horizontal (row) data set must be
tested against a vertical array? (Yes, I'm new to this.) "T. Valko" wrote: You have to make the array constant a vertical array. Using commas makes it a horizontal array. Replace the commas with semicolons: =SUMPRODUCT(--(A10:U10={"P";"B";"S";"V";"H";"PBSVH"})) -- Biff Microsoft Excel MVP "PFB" wrote in message ... The following formula works on a column range (A1:A40), but not on a row range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
I'm not sure if it's mentioned at this site or not but you'll find lots of
info: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "PFB" wrote in message ... Thanks! Where can I find info on why a horizontal (row) data set must be tested against a vertical array? (Yes, I'm new to this.) "T. Valko" wrote: You have to make the array constant a vertical array. Using commas makes it a horizontal array. Replace the commas with semicolons: =SUMPRODUCT(--(A10:U10={"P";"B";"S";"V";"H";"PBSVH"})) -- Biff Microsoft Excel MVP "PFB" wrote in message ... The following formula works on a column range (A1:A40), but not on a row range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT won't work on a row
I don't believe that it mentions transposing an array constant, but it does
mention transposing a range array using TRANSPOSE, which is the same effect, i.e. =SUMPRODUCT(--(A10:U10=TRANSPOSE({"P","B","S","V","H","PBSVH"})) ) -- __________________________________ HTH Bob "T. Valko" wrote in message ... I'm not sure if it's mentioned at this site or not but you'll find lots of info: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "PFB" wrote in message ... Thanks! Where can I find info on why a horizontal (row) data set must be tested against a vertical array? (Yes, I'm new to this.) "T. Valko" wrote: You have to make the array constant a vertical array. Using commas makes it a horizontal array. Replace the commas with semicolons: =SUMPRODUCT(--(A10:U10={"P";"B";"S";"V";"H";"PBSVH"})) -- Biff Microsoft Excel MVP "PFB" wrote in message ... The following formula works on a column range (A1:A40), but not on a row range (A10:U10). What have I missd? =SUMPRODUCT(--(A10:U10={"P","B","S","V","H","PBSVH"})) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should this work? =SUMPRODUCT( --($K$3:$K$4650&D3),... | Excel Discussion (Misc queries) | |||
I have never been able to get a SumProduct formula to work..Help! | Excel Worksheet Functions | |||
SUMPRODUCT vs. COUNTIF -- Why does one work? | Excel Worksheet Functions | |||
sumproduct doesn't work | Excel Worksheet Functions | |||
Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) |