Sumproduct Formula Question

[ststern45]

I have a range of cells some that contain blanks.

When I enter this formula I receive a #value

Counting the number of Odd and Even numbers in a range

=SUMPRODUCT(--(MOD(P10:AS10,2)=1),--(P10:AS10<>"")) Ctrl Shift Enter

Thanks in advance!!

 

[jim may]

Apparently you have something in a cell or 2 of your range of cells that contains a non-numeric.

Also, you don't need to enter as Control+Shift+Enter with the Sumproduct(). Try without the CSE First...

 

[ststern45]

The cells that are blank contain formulas the resulted from that calculation returning an empty cell.

 

[mfexcel]

Try:

=SUMPRODUCT(--IFERROR(MOD(A7:AD7,2)=1,0),--(A7:AD7<>""))

CTRL SHIFT ENTER is required.

The cells that are blank contain formulas the resulted from that calculation returning an empty cell.

 

[ststern45]

Thank you sooo much!!

This works great.

 

[Peter_SSs]

Slightly different take to Jim on using CSE with SUMPRODUCT. My take is that sometimes SUMPRODUCT will not work without CSE, but if you are using CSE with SUMPRODUCT there is likely a simpler function (often SUM with CSE) that can be used instead.

For the original question, a couple of alternatives to also consider.

Odd count: =SUM(IFERROR(MOD(P10:AS10,2),0))

Even count: =-SUM(IF(P10:AS10<>"",MOD(P10:AS10,2)-1))

both confirmed with CSE

 

[Aladin Akyurek]

The cells that are blank contain formulas the resulted from that calculation returning an empty cell.

I'd opt for a pair that is structured the same way:

=SUM(IF(ISNUMBER(P10:AS10),IF(MOD(P10:AS10,2)=1,1)))

=SUM(IF(ISNUMBER(P10:AS10),IF(MOD(P10:AS10,2)=0,1)))

Each must be confirmed with control+shift+enter. Note that ISNUMBER specifies the intent exactly.