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"}))

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"}))

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"}))

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"}))

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"}))

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"}))