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.