Previous posts (here and here) introduced you to filtering with SUMPRODUCT. It shows that SUMPRODUCT is a very powerful formula for filtering several AND criteria in both columns and rows. At times you may have a need for more complex criteria. This post will show you how to use multiple OR and AND criteria within SUMPRODUCT.
Imagine you work at a retail company. The company uses incentives in certain periods to boost customer sales. Your boss asks you for the performance of some of your employees working at the main store. The employees working here are Lisa, Michelle, Dennis, Boris, Jacob and Vera. The campaign has been active for the months April, June, August and November.
Below setup shows the data to retrieve.
A Naive Solution
One solution would be to write all the criteria in a single SUMPRODUCT formula. Since the time range is non-continuous, a manual formula is needed for both the name and month criteria. The example is created in below picture. The formula consists of three parts.
- The purple marked selects the data range (columns and rows).
- The second part is marked green and checks whether the names are the relevant ones for this analysis. It’s important that the data range in the rows is equal in length to the amount of rows in the first part.
- The third part is marked orange and selects the month numbers in the columns. Take a moment to note that the amount of columns in the data range is equal to the amount of columns in part 1 of this formula.
Both part 2 and part 3 test
criteria for being equal to one of the many values. In other words, the
operation uses SUMPRODUCT with multiple OR criteria. The month needs to be
equal to any of the indicated values, and the names can be any of the mentioned
employees. To be able to test for multiple values, you can add arrays to each
other with SUMPRODUCT. How does that work?
The plus sign in the before pictures forces the OR criterium. In part 2 of the formula, each individual array iterates the selection to match a name in the list. Below picture shows this logic. For Lisa the array returns zero for each cell except for row 10. It finds a match with the value “Lisa” there and returns number one. The same for Michelle in row 12 and Dennis in row 14. When these three arrays are added to each other, column M is the result.
Part 3 follows a similar pattern. Only this time the formula checks whether the month is equal to any of the campaign months. To get to the final result, part 1, 2 and 3 of the formula are multiplied with each other. This combines the AND and OR criteria in SUMPRODUCT. Remember. With SUMPRODUCT multiplying arrays results in an AND condition, and adding arrays results in an OR condition. Previous examples combines both to come to the end result. The parenthesis make sure that the AND and OR criteria are cut off at the right spot. You can dive into the example using the Example file:
As the paragraph title suggested, the solution before works, but is a naive solution. It requires lots of maintenance and is time consuming to write. There’s a better solution that you will find in part 2 of this series. Hope to see you there!