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 naïve 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.
Challenge
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.
Comparing Solutions
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.
Robust 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:
Conclusion
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!
Hi,
I have a range with coulmns a have countires names, column B have 5 KPIs ames for each country in column A.and than I have column c to column BF the numbers for each KPI. eg
A B wk1 wk2 wk3 wk4 wk5 ………………….wk52
Eu Hires 24 33 43 45 34 ………
Eu Exit 21 22 12 34 21
Eu Bench 4 6 11 2 13
Fr Hires 5 4 12 5 23
Fr Exit 23 12 32 11 3
Fr Bench 23 11 23 5 11
…
.,..
…
I want sum of Hires from wk1 to wk43 for Fr
How will I do that
A B Wk1 wk2 ……..are column Headings
please advise.
I gues sumproduct works on individual columns not a range.. I need to sum a range based on multiple col and rows condition
Could you please share a trick to apply the conditional format onto above scenario? I would appreciate it.
Further to my previous post, I’ve worked out my solution with the following formula. Please comment.
AND(ISNUMBER(MATCH($B13,$L$4:$L$9,0)),ISNUMBER(MATCH(C$12,$N$4:$N$7,0)))
Hi Rick,
I’m really learning a lot from excelgorilla thanks. I have a quick question hopefully 🙂
I have the below table. these are timesheets so to speak. I need to know the cost from the hours by the persons rate for a particular week (A to E columns)
PER RATE A B C D E
AA 100 1 7 5 6
BB 50 1
CC 150 1 3
DD 200 1 1 6 8
for example if I take week C the result should be persons CC and DD by their rates.
= (1*150)+(6*200)
Knowledge is power. And with great power comes great responsibility Sandeep. So thread carefully! ^^
simply MARVELOUS! That’s only I can say now; I’m overwhelmed.