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.
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)
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.
Step 2: Enter any two dates in the input field and check 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.
Step 1: Enter the formula in cell H6
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.
Step 2: Enter any two 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.
Step 1: Enter the formula in cell H6
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.
Step 2: Enter the dates and two sales then check the output
[ Note: Make sure Delivery date is in Date format and Sales cells are in the correct currency format.]
Things to Remember
- 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.
- This function is not applicable for searching wildcard characters as the SUMPRODUCT function does not support wildcard characters.
- If an array argument contains non-numeric values, they will be treated as zeros.
- SUMPRODUCT function is the alternative of an array formula.
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.