Tips for Excel, Word, PowerPoint and Other Applications

Advanced Applications of SUMPRODUCT (part 3)

Why It Matters To You

More fun with Advanced SUMPRODUCT. This time, we're going to sum up all of the odd values in a data set.

How to ...

You can use the example file, sumproduct_advanced3.xls, to follow along and see examples.

The sample data set is easy enough. For simplicity, I'm just going to use the numbers 1-10.

For this data set, we expect the total of all the odd numbers will be 25. In order to sum only the odd values, we have to figure out how to differentiate the odd from the even numbers. There are two ways to get at this.

Using a Simple Division

The first way is simply to divide each value and then take only those whose rightmost two values are ".5". The reason you have to encase the ".5" in quotes is because the RIGHT function returns a text result. If you use .5 as the qualifying criteria, the function will look for numerical values. Be careful not to select values where the rightmost 1 value is 5, otherwise you'll end up counting the value of 10. The formula looks like this and, as expected, results in a sum of 25.

SUMPRODUCT($B$5:$B$14,--(RIGHT($B$5:$B$14/2,2)=".5"))

Using the MOD Function

A more elegant solution utilizes the MOD function. The MOD function takes any number and a divisor and then returns the remainder. For odd values, if you select 2 as the divisor, the remainder should be 1.

This is what the same formula, using the MOD function, looks like. Again, as expected, it returns a value of 25. Note that in this case, we didn't have to encase the qualifying criteria in quotes.

SUMPRODUCT($B$5:$B$14,--(MOD($B$5:$B$14,2)=1))

So What?

This is powerful. A pivot table can't do this. This is a way to execute the summary in one easy formula. Also, if you use the MOD function, that you can dynamically specify what multiple you want to sum by.

Notes

Last updated	5/30/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	Basic SUMPRODUCT Advanced SUMPRODUCT (Part 1)
Related Tips	Advanced SUMPRODUCT (Part 2) Advanced SUMPRODUCT (Part 4)