Tips for Excel, Word, PowerPoint and Other Applications

Advanced Applications of SUMPRODUCT (part 4)

Why It Matters To You

Sometimes you need to only include values where your criteria is present (e.g., not blank) or not present (e.g., blank).

How to ...

Again, we reach into our tool bag and pull out SUMPRODUCT. You can use the example file, sumproduct_advanced4.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 the values I want to sum and a simple employee ID # as my criteria. As you can see, some of my employee IDs are missing.

For this data set, we expect the total of all values with corresponding employee IDs will be 37. The sum of values without employee IDs is 10. In order to sum only the blank or not blank values, we have to figure out how to differentiate the two. Fortunately, Excel has a built in logical function that can help us out - ISBLANK.

In a normal application, ISBLANK simply tests a cell and returns TRUE if the target cell is blank and FALSE if the target cell has anything in it. So, I'm going to apply the ISBLANK test to an array of employee ID's and the criteria I want to use for inclusion. The two formulas look like this:

SUMPRODUCT($C$6:$C$15,--(ISBLANK($B$6:$B$15)=FALSE))
SUMPRODUCT($C$6:$C$15,--(ISBLANK($B$6:$B$15)=TRUE))

=SUMPRODUCT(--(RANK(list_of_values,list_of_values,0)<=20),list_of_values) =SUMPRODUCT(--(SaleDate>=C11),--(SaleDate<=C12),--(EstShipDate>C12))

So What?

This is quite useful and I find myself using this structure often.

Notes

Last updated	7/15/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 3)