Counting values in every other column

[K Garvey]

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.

 

[T. Valko]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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.

 

[Rick Rothstein \(MVP - VB\)]

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