Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel SUMPRODUCT with Multiple Criteria in Same Column

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.


Download Practice Workbook

You can download our practice workbook from here for free!


Introduction to SUMPRODUCT Function in Excel

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.


4 Examples of Excel SUMPRODUCT with Multiple Criteria in Same Column

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: Solved] SUMPRODUCT with Multiple Criteria Not Working 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.

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: SUMPRODUCT for Counting with Multiple Criteria 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.

Read More: SUMPRODUCT Function with Multiple Columns in Excel (4 Simple Ways)


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.

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.


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.
  • SUMPRODUCT function is the alternative of an array formula.

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.

And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!


Further Readings

Md. Abdullah Al Murad

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