Tips for Excel, Word, PowerPoint and Other Applications
Selective Exclusion or Inclusion using SUMPRODUCT
Why It Matters To You
When we model data sets, it's often very useful to have the ability to selectively exclude specific data points or even entire sets of records. In this Tip, we explore a couple of basic functions used to selectively include/exclude specific data points. In a later tip, we'll learn about selectively including/excluding entire data sets.
Why This is Useful (examples)
| Requests for Proposal | Selectively exclude bids based on disqualification |
| Invoice review | Selectively exclude charges based on date |
| Surveys | Selectively include respondents based on respondant terminating |
| Home purchase options | Selectively include upgrade options based on budget |
| Spend analysis | Selectively include or exclude suppliers/GL codes |
Why selectively include/exclude and not just delete the extra data? More often than not, we would rather not destroy our original data. In addition, if we are evaluating several potential cases or endstates it's more efficient to have a mechanism that can temporary disable data vs. deleting data which permanently erases it.
How to ...
Here are a couple of the functions I use for selective include or exclude.
SUMPRODUCT. SUMPRODUCT simply calculates the sum of the products of corresponding arrays. This means the product of the 1st values in each range is calculated, then added to the product of the 2nd values …., then added to the product of the nth values.
Why does this work? Often times I'll use a binary value of 1 or 0 to indicate inclusion/exclusion, so anything marked as a 0 simply returns a 0 in the SUMPRODUCT, while every value marked as a 1 returns the same value.
SUMIF. Compares all values in a given range against a set criteria then for all cells that match, adds the corresponding value in the Sum_Range.
Why does this work? As long as you're consistant, anything marked as 'include' (e.g., 1, 'x', 'include', etc) in the Range gets summed, while any other value gets ignored.
ISBLANK. Simply returns a TRUE or FALSE depending on whether the target cells is blank or not.
Why does this work? If you'll remember from our discussion about logical math, TRUE statements are the equivalent of a 1 and FALSE statements are the equivalent of a 0. However, keep in mind that SUMPRODUCT does not work on an array of TRUEs and FALSEs.
Exercises and Examples
Open the sample data set for examples of how data can be selectively ignored using SUMPRODUCT and a simple flag and how data can be selectively included.Cool Combos
| ISBLANK x Value | Includes a value only if the target cell is blank |
| NOT(ISBLANK) x Value | Reverses the ISBLANK to include only if target cell has content |
| Product of multiple logical tests | Calculates an include flag based on multiple criteria |
| SUMPRODUCT(value, exclude) | Simplest case of a column of include flags |
| SUMIF(include values, include flag, value) | Lets you specifiy what you want to use an an include flag |
| Data Validation with SUMIF | The Validation controls inputs to make the SUMIF more robust |
Notes
| Last updated | 9/2/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | |
| Related Tips | Advanced SUMPRODUCT |