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 criteria 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:

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:

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 is 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:

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 criteria 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 hardcoding 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:

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.

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.