Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Sumproduct Multiple AND Criteria

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 more complex formulas are needed when you find your data in several columns. The 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 of June. Start your formula by selecting the data range (without names and months) and multiplying it by the array containing months that are equal to 6.

The formula is:

=SUMPRODUCT( (C9:N14) * (C8:N8 = 6) )

SUMPRODUCT First Criterion

Array1 of the formula instructs Excel to add up all the numbers in the selection C9:N14. Array2 checks for each month’s 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 criterion. 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?

SUMPRODUCT filter Month Range

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.

SUMPRODUCT add Month Range as 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

An earlier example showed criteria that looked at the months. You can do a similar operation for rows, as shown in the 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.

SUMPRODUCT criteria on both Rows and Columns

To account for 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 meets this condition, 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

The 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 the 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.
using break lines for clarity

Note: Spacing within your formula is fine when applied to some parts of the formula. Other parts cause an error. There are a few points to look out for.

First of all, be careful not to put spaces within a formula name (SUMPRODUCT, SUMIF, VLOOKUP, etc.).

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.

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!

Share this post:
  1. Notebook opened. Taking notes. Thanks for the valuable post. I wish more people would talk about this subject as in depth as you.

    Reply
    • That’s great to hear. I hope to be launching more in depth articles. Any topics you would be interested in?

      Reply
  2. 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!

    Reply
  3. Please solve
    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.

    Reply
    • Hi Digambar,

      Perhaps you can look into array formulas. You can enter an IF formula with multiple conditions as an array. Perhaps it solves your problem.

      Good luck!
      Rick

      Reply
    • Here is formula

      =IF($G$1150,(150*20)+($G$1-150)*10))

      put your desired value in cell “G1” (column ‘G’, Row ‘1’)

      Reply
    • Here is formula

      =IF($G$1150,(150*20)+($G$1-150)*10))

      put your desired value in cell “G1” (column ‘G’, Row ‘1’)

      Reply
  4. 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?

    Reply
    • 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.

      Reply
      • 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,

        Reply
      • If you have text cells in the array, you can use an IFERROR function right after the “SUMPRODUCT( ” and you’ll work it out

        Reply
  5. 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?

    Reply
    • 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

      Reply
      • 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?

        Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.