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.
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.
Minor point, but note that the ‘=1’ part of the MOD criteria is unecessary, as the function will return 1 or 0 anyway.
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
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
Indeed, I think every Excel user should have
http://www.xldynamic.com-source-xld.SUMPRODUCT.html
in their favourites, it’s a clear milestone.
I’ll be surprised if anyone can make the formula shorter than this;
=SUMPRODUCT((A< =B14)*(A>=B13)*(MOD(A;2)*(A)))
given that A1:A10 is named A.
I think this is shorter: {=SUM((A
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.
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.