I'm trying to add only "odd" numbers from a range(named "data") which contains numbers, blank cells and text. No matter what I try I always seem to end up with a #value error. My latest attempt is as follows: =SUMPRODUCT(N(MOD(Data,2)=1),Data) Any suggestions?

The reason your formula "=SUMPRODUCT(N(MOD(Data,2)=1),Data)" not working is that Excel will sum EVERY row of "Data". As some value in "Data" is not a number, #VALUE error returned. – wilson Oct 29 '10 at 2:03
Use Array Formula
=SUM(IF(ISNUMBER(data), IF(MOD(data, 2) = 1, data, 0), 0))
Press Ctrl+Shift+Enter after entering the formula
(Tested in Excel 2003)

1



1@TuxRug: This is called ARRAY FORMULA. It makes a function accept array as argument and return an array. For example, in this case, assume "data" is [1, "a", ""], then ISNUMBER(data) will return [true, false false]. Some reference: office.microsoft.com/enus/excelhelp/… – wilson Nov 1 '10 at 7:11

This answer from Wilson worked a treat, but, when trying to adapt it to "COUNT" instead of "SUM" it fails! Why is this? – Auto Nov 3 '10 at 2:02