Md. Abdullah Al Murad
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 function. In this article, I will show you 4 practical examples of the SUMPRODUCT function with multiple criteria in the same column.
Introduction to SUMPRODUCT Function in Excel
The syntax of the SUMPRODUCT function is like this:
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.
Excel SUMPRODUCT with Multiple Criteria in Same Column: 4 Examples
Say, you have a dataset of product ids, names, sales, and delivery dates. Now, you want to sum up several conditions using the SUMPRODUCT function.
1. Using SUMPRODUCT Formula with AND Logic to Match Multiple Criteria in Same Column
Say, you need to calculate the total sales for products with a delivery date between 15 July 2021 and 17 September 2021. Here, you will have to use AND logic with the SUMPRODUCT function. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on cell H4 and record the starting date.
- Afterward, click on cell H5 and insert the end date.
- At this time, click on cell H7 and insert any of the following formulas.
=SUMPRODUCT(--(E5:E12>=H4), --(E5:E12<=H5), D5:D12)=SUMPRODUCT((E5:E12>=H4)*(E5:E12<=H5), D5:D12)- Subsequently, hit the Enter key.
As a result, you will get the total sales for products delivered between 15 July 2021 and 17 September 2021 by applying the SUMPRODUCT function with multiple criteria in the same column.
Note: Make sure the Delivery Date is in Date format and Sales cells are in the correct Accounting format.
Read More: How to Use SUMPRODUCT with Criteria in Excel
2. Using SUMPRODUCT Formula with OR Logic
Now, it might happen that you need a product count which has a sales amount of 2500 or 4500. In this case, you can use the SUMPRODUCT function with multiple OR criteria in the same column. Follow the steps below to do this.
📌 Steps:
- At the very beginning, click on cell H4 and insert the first sale’s value of your desired range.
- Afterward, click on cell H5 and insert the last sales value of your desired range.
- Following, click on cell H7 and insert the formula below.
=SUMPRODUCT((D5:D12=H4)+(D5:D12=H5))- Subsequently, hit the Enter key.
As a result, you will get the count for products that have sales of 2500 or 4500.
Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel
3. Using SUMPRODUCT Formula with Both AND & OR Logics
Now, say you need to find the count of products that have delivery dates after 13 Aug 2021 and sales of $2500 or $3500. So, you have to embed AND and OR logic here in the SUMPRODUCT function. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on cell H4 and insert your required checking delivery date.
- Afterward, insert the values of the sales on cells H5 and H6.
- Afterward, click on cell H8 and insert the following formula to accomplish your result.
=SUMPRODUCT((E5:E12>H4)*((D5:D12=H5)+(D5:D12=H6)))- Subsequently, press the Enter key.
Consequently, you will get your product count as your requirements.
Note: Make sure the Delivery Date is in Date format and Sales cells are in the correct Accounting format.
Related Content: Excel SUMPRODUCT Function Based on Date Range
4. Using SUMPRODUCT for Multiple Date Criteria in Same Column
Now, say, you need to calculate the total sales for products of multiple date ranges. Say, you need to calculate total sales for the delivery date between 13 August 2021Â to 19 October 2021 and between 14 December 2021 to 18 December 2021. Follow the steps below to do this.
📌 Steps:
- Initially, put all the date range values on cells G5:H6.
- Next, click on cell H8.
- Subsequently, insert the following formula and hit the Enter key.
=SUMPRODUCT((E5:E12>=TRANSPOSE(G5:G6))*(E5:E12<=TRANSPOSE(H5:H6))*D5:D12)
Thus, you will get the total sales for products delivered between these two date ranges.
Note: Make sure the Delivery Date is in Date format and Sales cells are in the correct Accounting format.
Similar Readings
Things to Remember
- To avoid the #VALUE! The error we must ensure that all arrays in a SUMPRODUCT formula have the same number of columns and rows.
- This function is not applicable for searching wildcard characters as the SUMPRODUCT function does not support wildcard characters.
- If an array argument contains values that are not numeric, SUMPRODUCT will treat them as zeros.
- The SUMPRODUCT function is the alternative of an array formula.
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, I have shown you 4 practical examples of the SUMPRODUCT function with multiple criteria in the same column in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
