SUMPRODUCT Multiple Criteria Same Column (3 Useful Ways)

Enter sales and see the output

When we need to compare data in two or more ranges and calculate data with multiple criteria or conditions, Excel provides a function named SUMPRODUCT. This function is the alternative to arrays in Excel and it is comfortable to handle array-related tasks with this SUMPRODUCT function. In this article, we will see how to use the SUMPRODUCT function for multiple criteria in the same column.

Download the Practice WorkBook

What is the SUMPRODUCT Function? 

The syntax of the function is like this:

=SUMPRODUCT(array1, [array2], [array3], ...)

It takes the array in its parameter and returns the sum. In this function, we can pass multiple arrays to get the total sum product. For more details, you can visit this Link

3 Ways to Use SUMPRODUCT Multiple Criteria Same Column

1. Using SUMPRODUCT Formula with AND Logic

Let’s see how we can get summation using the SUMPRODUCT function where we will follow AND logic. That means in the multiple criteria we need to consider all the conditions must be true.

For showing the formula let’s assume we have a dataset of some Apple products with their delivery dates and sales.

 Using SUMPRODUCT Formula with AND Logic

Our task is to find the total sales on a certain date.

Step 1: Enter the formula in cell H6 and press Enter

=SUMPRODUCT(--(E4:E11>=H3), --(E4:E11<=H4), D4:D11)

Or

=SUMPRODUCT((E4:E11>=H3)*(E4:E11<=H4)*D4:D11)

Formula Explanation

The first part of the formula –(E4:E11>=H3) finds the dates greater than or equal the start date and  –(E4:E11<=H4) is for the end date. Lastly, as we want the total sum of the sales that’s why  D4:D11 is declared to define the range.

For the alternative formula, we are just using multiplication for counting the cells and summing them up.

Enter the formula using SUMPRODUCT and press enter

Step 2: Enter any two dates in the input field and check the output

Enter dates and see the output

[ Note: Make sure your dates are in Date format and the total sales output cell is in the actual correct currency format.]

2. Using SUMPRODUCT Formula with OR Logic

We can use this function for the OR logic also. Or logic means if any of the conditions are true then it will be considered. For this method, we will use the same dataset above but here we will consider the Sales column. We will find the total count if our sales are equal to 2500 or 4500.

Using SUMPRODUCT Formula with OR Logic

Step 1: Enter the formula in cell H6

=SUMPRODUCT((D5:D12=H4)+(D5:D12=H5))
Sample content

Formula Explanation

Here (D5:D12=H4) this part will be finding the cells which are equal to our first given sales and (D5:D12=H5) this is done the same thing for the second sales. As we have used plus sign (+) this will work as an OR operation.

Enter the formula

Step 2: Enter any two sales and see the output

Enter sales and see the output

3. Using SUMPRODUCT Formula with both AND & OR Logic

Now let’s see how we can use this function for both the AND and OR logic at a time. This method will be using the same dataset which was used in the previous method. Here we will apply the formula in the Sales column. Our target is to count the products after a specific date and it should be matched within two given sales. To show this method must need at least Two Columns for criteria.

 For Or and AND

Step 1: Enter the formula in cell H6

=SUMPRODUCT((E4:E11>H3)*((D4:D11=H4)+(D4:D11=J4)))

Formula Explanation

Here ((D4:D11=H4)+(D4:D11=J4)) this is our OR part and when we are multiplying this with (E4:E11>H3) part then it is behaving like an AND operation. E4:E11>H3 this compares if the date is after the given date and D4:D11=H4, D4:D11=J4 is finding if it matched with any of the two given dates.

 Enter formula

Step 2: Enter the dates and two sales then check the output

 See the result of OR and AND

[ Note: Make sure Delivery date is in Date format and Sales cells are in the correct currency format.]

Things to Remember

  1. To avoid the #VALUE! The error we must ensure that all arrays in a SUMPRODUCT formula must have the same number of rows and columns.
  2. This function is not applicable for searching wildcard characters as the SUMPRODUCT function does not support wildcard characters.
  3. If an array argument contains non-numeric values, they will be treated as zeros.
  4. SUMPRODUCT function is the alternative of an array formula.

Conclusion

These are the ways to SUMPRODUCT for multiple criteria within the same column. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo