In previous post you learned how to combine AND and OR criteria for SUMPRODUCT. The approach results in a long and difficult to maintain formula. It requires you to write an array for each criteria that you want to add. This can become an arduous task whenever your formula needs to look for many values. That’s why it’s a naive approach. In case your criteria needs to match any value out of a set of values, there’s a trick you can use to make your life easier.
In the last post you saw a first solution to get the results for your boss. You still work at the same retail company and know that your boss is likely to change his mind along the way. Therefore your new formula should:
- allow to easily change lookup values.
- handle non-continuous time ranges.
When brainstorming about it, you realize the formula should not have hard coded values. This is a two-fold challenge.
First some values are text, the names. There should be a reference to the names so they can easily be changed without editing the formula itself. Direct references make it difficult to add new names. For each name a new array is necessary in the formula. A way to approach it is to add a list of dummy (read empty) references, which you can fill with new names later. Still the amount of new names you can add is limited to the amount of dummy references added. Not a future proof solution.
Second, there’s the challenge of including non-continuous periods. When a period is continuous a simple period >= x and period <= a would do. Yet it may very well be that the selected periods are non-continuous. For this two-fold problem, using lists containing the values to match would solve the problems. So how can you match a set of values?
Match a Set of Values
Through a combination of the formulas ISNUMBER and MATCH you can provide a list as input for SUMPRODUCT. The usage is best shown through an example. Solution 1 in below pictures adds each condition as a seperate array. You can see this in previous post. Let’s focus on solution 2.
Solution 2 multiplies 2 arrays. The base array contains the number values. The second array uses a combination of ISNUMBER and MATCH. How does that work? The MATCH formula accepts an array as its input. Lookup values are put as a first argument, and the array to look for as second argument. Each individual name in the full name list, will be matched with the Lookup Values list. If a name exists, the position in the array is returned as number, otherwise the formula returns #N/A. The resulting array will look something like:#N/A, #N/A , 1, #N/A , 2, #N/A , 3, #N/A , #N/A , #N/A , #N/A , 4, #N/A , #N/A , #N/A , #N/A )
There’s 2 problems with this array. In earlier examples, the array that matches the names would return 1 (TRUE) or 0 (FALSE). In this example the position of the Lookup Value is returned. These can be bigger than 1. Multiplying this array by the array containing the numbers values can therefore result in a multiplication of the values. And secondly, multiplying array values by #N/A results in an error.
To make this formula work you can use the ISNUMBER() formula. By wrapping the array in the ISNUMBER() function, all #N/A will be treated as 0 (FALSE), and all numbers as a 1 (TRUE). Now multiplying the two arrays results in the total sum of the values in the lookup list.
Solution 1 uses SUMPRODUCT and adds a seperate array for each name condition. This example contains 4 conditions to match. When adding new names, each name requires a new array. Now imagine your dataset is bigger, and you need to match 20+ names. The formula would become huge, prone to error and lots of work to change.
Solution 2 uses SUMPRODUCT in combination with ISNUMBER() and MATCH(). As long as the lookup condition is similar (looking up a name in this case), you can provide a list of lookup values in a single array. And it can be as long as you wish. If you want to look up 20+ different names, all you have to do is provide the list of names. If you would add this to a table, the MATCH formula could reference the entire list of names within the table and you would never have to adjust the formula at all. Let’s go back to the example from previous post and provide a better solution.
The example in previous post looks for both names and (non-continuous) periods. The data used looks as in below pictures.
Using what we learned in the last chapter we can provide SUMPRODUCT with 2 arrays using ISNUMBER() and MATCH(). The first array looks for the Filter List with Months, and the second array specifies the required names. So have we achieved the challenge?
In above solution it’s easy to change values, as the formula does not need to be changed. Also non-continuous periods can easily be supplied to the list. For the purpose of the picture the list is put above the dataset. Feel free to move the lists to a place in the spreadsheet where the list can be as long as you want without covering the data. In case you’d like to review the formulas you can find these in the example file:
Now you know how to put SUMPRODUCT to use in a flexible and robust way. Did you know you can also provide the rows of a pivot table to the SUMPRODUCT array? In that way your formula can even become slicer dependent. But that’s for another blogpost!