Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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
    
@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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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