# How to Use SUMPRODUCT With Multiple AND Criteria

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 SUMIF. 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. The goal is to use SUMPRODUCT with multiple conditions to add numbers from multiple columns. This page starts with a basic example with a single criteria. From here it will build up to more advanced examples using several criteria.

**SUMPRODUCT with a Single Criteria**

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.

**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) )**

**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: **Multiple Criteria – Example File**

**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.

**Wrap up**

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.

Soon I will launch a post explaining how you can add a series of AND statements to a SUMPRODUCT formula. You can do that using a very simple trick that saves you a lot of formula writing. So keep your eyes open for the follow up post.

I hope you liked this post. If you did, share it with your network. And please leave a comment below with questions or suggestions.

SP is the flavour of the season!

Still love the detail and understandability of your blog posts!

thanks Frank 🙂

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?

Hi Novie,

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!

Rick

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 :

=SUMPRODUCT((FC!$A$3:$A$3328=BO!$E5)*(FC!$B$3:$B$3328=BO!$A5)*(FC!$E$2:$BH$2=BO!F$4)*(FC!$E$3:$BH$3328))

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?

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.

Any solutions?

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.