Summing Odd Numbers

I was cleaning out my Inbox yesterday, looking at some of the Excel questions people have sent me. For whatever reason, I didn’t have time to answer this one when it came, but today I had to make sure that I knew how to do it.

How do you sum only odd numbers in a range and only if the numbers are between two numbers? I use an array formula. To see how array formulas work, see Anatomy of an Array Formula.

SumOdds

All the expressions return arrays which are multiplied together. The first three parenthetical expressions limit the range that is summed. They return arrays of Trues and Falses which are subsequently converted to 1’s and 0’s. The expression with the MOD function returns Trues for only those entries that are odd. The MOD function basically says that if you divide by 2, only return True if the remainder is 1.

Don’t forget to enter array formulas with Control+Shift+Enter, not just Enter. Excel will put the curly braces in there for you.

Posted in Uncategorized

7 thoughts on “Summing Odd Numbers

  1. Minor point, but note that the ‘=1’ part of the MOD criteria is unecessary, as the function will return 1 or 0 anyway.

  2. Hi Ken
    I would use the non-array entered SUMPRODUCT alternative here :-)
    =SUMPRODUCT((A1:A10>=B13)*(A1:A10< =B14)*(MOD(A1:A10,2)=1)*(A1:A10))

    or
    =SUMPRODUCT((A1:A10>=B13)*(A1:A10<=B14)*MOD(A1:A10,2)*(A1:A10))

    Frank

  3. Hi
    I would use the non-array entered SUMPRODUCT alternative here :-)
    =SUMPRODUCT((A1:A10>=B13)*(A1:A10< =B14)*(MOD(A1:A10,2)=1)*(A1:A10))

    or
    =SUMPRODUCT((A1:A10>=B13)*(A1:A10<=B14)*MOD(A1:A10,2)*(A1:A10))

    Frank

  4. You’re absolutely right. The array formula is very nearly the same as the ones in Dick’s post “summing every Nth row”, 8th of June, under Worksheet Functions. This website is even greater then I thought.

  5. Excluding the =1 in later formulas makes it less portable. Leaving the =1 makes it much easier to edit the formula for even numbers or multiples of 4, 5 etc.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.