Using SUMPRODUCT with multiple AND criteria is can be very powerful. There are situations where several criteria are required to add up the right numbers. That’s straightforward if the numbers are found in a single column using the SUMIF function.
Yet when you find your data in several columns, more complex formulas are needed. Previous post explained the basics of a SUMPRODUCT formula. This post continues on that foundation.
This page starts with a basic example with a single criteria. From here it will build up to more advanced examples using several criteria. The end goal is to use the SUMPRODUCT function with multiple conditions to add numbers from several columns.
Table of contents
1. SUMPRODUCT with a Single Criterion
The below data set contains 6 names in the rows and 12 months in the columns. Imagine your boss wants you to add up all the numbers for the month June. Start your formula by selecting the data range (without names and months) and multiply it by the array containing months that are equal to 6.
The formula is:
=SUMPRODUCT( (C9:N14) * (C8:N8 = 6) )
Array1 of the formula instructs Excel to add up all the numbers in the selection C9:N14. Array2 checks for each month number whether it is equal to 6. The result of this is either TRUE (1) or FALSE (0). You can see the outcome of this in row 16.
Only for column H the outcome is TRUE, so the result is 1. To multiply Array1 by Array2 means multiplying the actual numbers by 0 or 1.
In this example, the formula multiplies all numbers in column C by 0, and all numbers from column H by 1. All the values that are multiplied by 0 add up to zero. The only numbers left are the multiplied by 1, in this case month 6.
2. SUMPRODUCT with Multiple Criteria for Columns
Let’s continue the above example by adding another criteria. Your boss now wants to know what the sales are in Q2, so from month 4 up to and including month 6. Meaning we have a bigger range of data to add up. How do you handle that?
The formula to use is:
=SUMPRODUCT( (C9:N14) * (C8:N8 >= 4) * (C8:N8 <= 6) )
There are 3 months that belong to Q2. Notice that you don’t need three statements to specify each month. Instead you can use a construction where the months are bigger or equal to 4 (>=4) AND smaller or equal to 6 (<=6).
Often you will want to make this formula as dynamic as possible. Not everyone is as comfortable changing complex formulas.
Yet most people are perfectly fine with adjusting a number in a cell. Instead of hard-coding the months to add, you can refer to cells that specify these values. These cells are often called parameters.
To achieve that, add the lower limit month in cell J4 and the higher limit in cell J5. Now change the formula to reflect this. Instead of referring to number 4, write cell J4. Replace number 6 by cell J5.
The end formula will be:
=SUMPRODUCT( (C9:N14) * (C8:N8 >= J4) * (C8:N8 <= J5) )
3. Multiple Criteria for Columns and Rows
Earlier example showed criteria that looked at the months. You can do a similar operation for rows, as shown in previous post. The real power of SUMPRODUCT shows when you include conditions for both rows and columns.
Your boss asks you to not only show the sales numbers for Q2, but he’s specifically curious about Lisa’s results. You smile at him and tell him that’s no problem.
To account for an extra criteria in the rows, add another array to the formula. Specify that the names should be equal to ‘Lisa’. As we just learned, you may want to refer to a parameter cell instead of hard-coding it. So write Lisa in cell M4.
Now add an array selecting cells B9:B14 being equal to your parameter cell, in this case M4. Column P shows which of the rows meet this condition, being row 11.
The formula to use is:
=SUMPRODUCT((C9:N14) * (C8:N8 >= J4) * (C8:N8 <= J5) * (B9:B14 = M4) )
This adds up to a total of 2,164. Those are the sales in Q2 from Lisa. If you want to follow along, you can find the example file here:
Use Break-Lines for Clarity
Previous example had 3 criteria and a total of 4 arrays. Once this amount increases, it might get more difficult to read your formulas. For presentation purposes you can therefore choose to add break-lines. These allow you to move text from your formula onto a next line. To do that:
- Put your cursor before the part you want to move to the next line
- Press Alt + Enter
- Use spacing to move your formula to the desired format.
Note: Spacing within your formula is fine when applied to some parts of the formula. Other parts cause an error. There’s a few points to look out for.
First of all, be careful to not put spaces within a formula name (SUMPRODUCT, SUMIF, VLOOKUP e.g.).
Secondly, avoid spaces within the range selection (C9:N14, C8:N8 in this example).
Lastly, don’t split up operators (<=, <>, <=). If you consider these three points, you should be good to go! It will greatly increase the readability of your formulas
As shown in previous examples, SUMPRODUCT has the ability to add up both columns and rows. This makes it a much more versatile formula than the well-known SUMIF formula. You can add many different criteria to meet your needs. As you use the formula more often you will quickly find it your go-to formula.
In the next post of this series you can find how you can add a series of AND and OR statements to a SUMPRODUCT formula. But at times you may need a solution for writing many conditions in your formula.
This advanced post explains how you can add many conditions to SUMPRODUCT using a simple trick that saves you a lot of formula writing. I encourage you to check it out.
Thanks for visiting and I hope to see you back next time!
I enjoyed your write-up a lot! I really just like just how an article writer clarified the concepts.
Notebook opened. Taking notes. Thanks for the valuable post. I wish more people would talk about this subject as in depth as you.
That’s great to hear. I hope to be launching more in depth articles. Any topics you would be interested in?
The more I read, the greater your material is.
I have covered a lot of the other sources, like this on https://kunenerak.org/students-guide-dating-dont-own-car/nonetheless, only here, I’ve found valid information with such necessary facts
to keep in mind. I suggest you will publish articles
with many topics to update our knowledge, mine in particular.
The language is just another thing-just brilliant!
I believe I have already found my perfect source of the most up-to-date information, thanks to you!
Thanks for your kind words Andrew. I sure hope to be posting again soon. Hope to see you again soon
I have a two logic
1. if value is equal to 150 then multiply 20
2. if value is more then 150 the multiply up to 150 20 and more the 10 and both will added
suppose 148 then result is =148*20=2960
and 151 then result is =150*20+1*10= 3010
please give a formula to solve this problem.
Perhaps you can look into array formulas. You can enter an IF formula with multiple conditions as an array. Perhaps it solves your problem.
Probably the same problem I’m having – where if there is text in any field in a SUMPRODUCT – you get the #VALUE error.
What i’m trying to do is use different columns – which this only works if ALL the Columns is Numerical. But if one column between has text – it won’t work.
Hi Rob. Not all columns used in SUMPRODUCT are allowed to have text. It’s okay to have text when the range you select is part of a criteria. For example when you test for values being equal to the name “Lisa” in my example above. The test is than transformed into TRUE and FALSE values (one’s and zero’s ). Just make sure that the array that needs to add all the numbers, only consist of numbers and you should be fine.
Assume in the Example for Months i replace numbers 1, 2. 3 , …, 12 with Jan, Feb, Mar …., Dec. Of course, i specify the function arguments in Column J as usual. The formula returns zero implying it is not recognizing something. is it because am using AND instead of OR, please help.
N.B Assume i don’t have numbers, the = for Q2 would not apply. i equate the array2, 3, 4 to the function arguments. it is not working,
If you have text cells in the array, you can use an IFERROR function right after the “SUMPRODUCT( ” and you’ll work it out
hi, i’m having difficulty right now. i use sumproduct very often, but in this case, i got #value result. already check if there any mistake, but i haven’t found one. Can you explain what are the problems that can give #value result?
How unfortunate you’re experiencing an error in your formula. Is it possible for you to share an example file? I’ll see if I can help you out!
i’m sorry i can’t share an example file. Because when i made an example and test the formula, it works.
So, i’m trying to sum using two columns and one row as a criteria. I’m working in two sheets. This is the formula :
I put the array in the last part of the formula. Is there any problem with that?
Formula looks good to me Novie. Rob might have the solution for your problem. Could there by any other value than numbers within the data ranges selected?
This formula is not working in my worksheet. I have same problem
Still love the detail and understandability of your blog posts!
thanks Frank 🙂
SP is the flavour of the season!