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. This method allows more flexible criteria than SUMIF(). – Mike Fitzpatrick Oct 29 '10 at 1:59
  • Nice answer. It will work in 2007 as well. – Rhys Gibson Oct 29 '10 at 2:09
  • What does the CTRL-SHIFT-Enter do differently? – TuxRug Nov 1 '10 at 5:01
  • 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/en-us/excel-help/… – 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.