Excel SUMPRODUCT with Multiple Criteria in Same Column (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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:

=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.


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.

Sample Dataset to Apply SUMPRODUCT Funciton with Multiple Criteria in Same Column


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.

Starting Date of Checking Range

  • Afterward, click on cell H5 and insert the end date.

Ending Date of Checking Range

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

SUMPRODUCT Formula to Apply Multiple Criteria in Same Column

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, you might need a product count that 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.

First Sale Value to Check

  • Afterward, click on cell H5 and insert the last sales value of your desired range.

Second Sale Value to Check

  • Following, click on cell H7 and insert the formula below.
=SUMPRODUCT((D5:D12=H4)+(D5:D12=H5))
  • Subsequently, hit the Enter key.

Fomrula to Apply SUMPRODUCT Formula with OR Logic in Multiple Criteria in Excel

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.

Checking Delivery Date

  • Afterward, insert the values of the sales on cells H5 and H6.

Sales Values to Check

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

Formula to Apply SUMPRODUCT with Multiple Criteria in Excel

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 the 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.

Checking Delivery Date Values

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

SUMPRODUCT Formula to Apply Multiple Criteria in Same Column

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.

Read More: How to Use SUMPRODUCT IF in Excel


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 to 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.


Related Articles


<< Go Back to Excel SUMPRODUCT Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo