I need to evaluate the value in every other column of a row and determine if
it equates to 1,2,3,4,5,6,7,8,9,10 or 11. Each row has a player followed by
each event entered. If the player placed 1 through 11th place, these values
are in the row under each event in every other column (ie, B11:BW11). The
values 1,2,3,4,5,6,7,8,9,10,11 exist in a contiguous array in row 9, in the
range BX:CH. I am trying the following formula, but it doesn't evaluate
correctly:
=SUMPRODUCT(--(MOD(COLUMN($B13:$BW13)-COLUMN($B13)+0,2)=CG$9),(ISNUMBER($B13:$BW13)),--($B13:$BW13=0))

Looks like I need some help.

A couple of things...

You're missing the "--" in front of the ISNUMBER array.

Why are you adding 0 to the column array? COLUMN($B13)+0 That's not doing
anything!

What's in CG9?


--
Biff
Microsoft Excel MVP


"K Garvey" <K wrote in message
...
I need to evaluate the value in every other column of a row and determine
if
it equates to 1,2,3,4,5,6,7,8,9,10 or 11. Each row has a player followed
by
each event entered. If the player placed 1 through 11th place, these
values
are in the row under each event in every other column (ie, B11:BW11). The
values 1,2,3,4,5,6,7,8,9,10,11 exist in a contiguous array in row 9, in
the
range BX:CH. I am trying the following formula, but it doesn't evaluate
correctly:
=SUMPRODUCT(--(MOD(COLUMN($B13:$BW13)-COLUMN($B13)+0,2)=CG$9),(ISNUMBER($B13:$BW13)),--($B13:$BW13=0))

Looks like I need some help.

I've read your (short) description and looked at your formula several
times... and I'm not sure what you are trying to accomplish with the
formula. Can you perhaps add a little more description to explain what you
need or want? Maybe providing a couple of examples would help.

Rick


"K Garvey" <K wrote in message
...
I need to evaluate the value in every other column of a row and determine
if
it equates to 1,2,3,4,5,6,7,8,9,10 or 11. Each row has a player followed
by
each event entered. If the player placed 1 through 11th place, these
values
are in the row under each event in every other column (ie, B11:BW11). The
values 1,2,3,4,5,6,7,8,9,10,11 exist in a contiguous array in row 9, in
the
range BX:CH. I am trying the following formula, but it doesn't evaluate
correctly:
=SUMPRODUCT(--(MOD(COLUMN($B13:$BW13)-COLUMN($B13)+0,2)=CG$9),(ISNUMBER($B13:$BW13)),--($B13:$BW13=0))

Looks like I need some help.

not sure what you are trying to accomplish with the formula.

As posted, the formula would count numeric 0 in the range of columns where
the mod = CG9.

The -COLUMN($B13)+0 stuff makes it robust against column insertions but the
+0 is not needed.

So, it all depends on what's in CG9 as to which columns are calculated.


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I've read your (short) description and looked at your formula several
times... and I'm not sure what you are trying to accomplish with the
formula. Can you perhaps add a little more description to explain what you
need or want? Maybe providing a couple of examples would help.

Rick


"K Garvey" <K wrote in message
...
I need to evaluate the value in every other column of a row and determine
if
it equates to 1,2,3,4,5,6,7,8,9,10 or 11. Each row has a player followed
by
each event entered. If the player placed 1 through 11th place, these
values
are in the row under each event in every other column (ie, B11:BW11). The
values 1,2,3,4,5,6,7,8,9,10,11 exist in a contiguous array in row 9, in
the
range BX:CH. I am trying the following formula, but it doesn't evaluate
correctly:
=SUMPRODUCT(--(MOD(COLUMN($B13:$BW13)-COLUMN($B13)+0,2)=CG$9),(ISNUMBER($B13:$BW13)),--($B13:$BW13=0))

Looks like I need some help.

not sure what you are trying to accomplish with the formula.

As posted, the formula would count numeric 0 in the range of columns where
the mod = CG9.

The -COLUMN($B13)+0 stuff makes it robust against column insertions but
the +0 is not needed.

So, it all depends on what's in CG9 as to which columns are calculated.

LOL... I have to learn to read better... I just went back to re-read the
original post again (mainly because you answered it once and seemed still
not to be as puzzled in your response to me as I was in my initial
reading)... I totally overlooked the "W" in the "BW" references and read
every range as $B13; so I couldn't understand why the OP was using a
SUMPRODUCT on a single cell instead of a range. I look at it now and the W's
are clear as can be; but I would swear they weren't there when I first read
(and re-read) the original posting... weird.

Rick