Why is #VALUE! appearing

[khank]

I'm looking to SUM the "odd" and "even" numbers.

In this case below there should be 2 "odd" and 3 "even" numbers.

N13 8
O13 5
P13 0
Q13 7
R13 4
S13 <blank>

The coding I'm using is:

For "ODD" in cell T13:
=SUMPRODUCT(--(MOD(N13:S13,2)=1))

For "EVEN" in cell U13:
=SUMPRODUCT(--(MOD(N13:S13,2)=0))-COUNTIF(N13:S13,"")

This coding was working, now appearing is the terrible "#VALUE!" in both cells. The cell format is "General" on all cells.

Again I cleared the cells and re-entered the coding – and still the same.

Any suggestions???

[Colin Legg]

Given the values you've described and the #VALUE! error result, I would check S13 carefully. Perhaps it contains an empty string?

Feel free to attach the workbook for us to see?

[martindwilson]

try array entered
=SUMPRODUCT(--(MOD(IF($N$13:$S$13="",0,$N$13:$S$13),2)=1),--($N$13:$S$13<>"")) for odd and
=SUMPRODUCT(--(MOD(IF($N$13:$S$13="",0,$N$13:$S$13),2)=0),--($N$13:$S$13<>"")) for even

[khank]

It code works perfectly.

Depending on the input that day sometimes I'll have a blank cell or two of the six - and on the other hand some days all six are filled.

Thank you for the revised coding and Colin thanks for your input.