Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
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
up vote 5 down vote accepted

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)

share|improve this answer
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
    
Thanks for that. It worked a treat. – Auto Oct 30 '10 at 20:14
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .