[Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel (3 Solutions)

Example 1 – If the Specified Arrays Are Not Same

One of the biggest problems users face while dealing with the SUMPRODUCT function is when they fail to input the same dimension of arrays. In the following sample dataset, we have column headers for Branch Number, Quantity and Price Per Unit. We need to find the total price of all quantities in cell D17.

If the Specified Arrays Are Not Same

  • Enter the following SUMPRODUCT function in the D17 cell.
=SUMPRODUCT(C5:C15,D5:D12)
  • C5:C15 refers to the Quantity of Fruit Items and D5:D12 refers to the Price Per Unit of the Quantities from D5 cell to D12 cell.

If the Specified Arrays Are Not Same

  • Press ENTER.

The output will be #VALUE!.

SUMPRODUCT function with multiple criteria not working
The ranges used in the SUMPRODUCT function should be equal in Rows. We have used C5:C15 as the first range, which has 11 Rows, and D5:D12 as the second range with 8 Rows. These two ranges don’t have an equal number of Rows. This is the reason why the SUMPRODUCT function has given an error as output.

Solution:

We have to take an equal number of Rows as ranges.

  • Enter the following formula in the D17 cell.
=SUMPRODUCT(C5:C15,D5:D15)

The number of Rows of the two ranges are equal, i.e. 11 rows.

SUMPRODUCT function with multiple criteria not working

  • Press ENTER.

The output becomes valid and result is 3318.

Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel


Example 2 – If Any Cell Contains Text

When one or more cells in the referenced range contain text or are formatted as a text data type, we’ll get a #VALUE! Error or #NAME? Error. The text could be the result of a calculation from another formula. Another reason could be that the cell is not formatted correctly.

Suppose we have a text in the D9 cell, and other cells in the same Column contain General Numbers. We need to find the total price of all quantities in cell D17.

If Any Cell Contains Text

  • Enter the following formula in the D17 cell.
=SUMPRODUCT(C5:C15,D5:D15)
  • On pressing ENTER, we’ll see the output as #Name!.

SUMPRODUCT function with multiple criteria not working
Solution:

If we replace the text of the D9 cell with a General Number, we’ll get a valid output. We have replaced the text with 12 and we will get the output 3318.

Related Content: Excel SUMPRODUCT Function Based on Date Range


Example 3 – If Formula Has Error in 3D Reference

If we use the SUMPRODUCT function and take ranges from single or multiple sheets and these sheet names are included in Name Manager as names, we’ll face errors while using it.

Suppose we have 3 datasets of Sales of Fruits in individual months.

The first dataset is the Sales of January.

If Formula Has Error in 3D Reference

The second dataset is the Sales in February.

SUMPRODUCT function with multiple criteria not working
The third dataset is the Sales in March.


We will find the Total Sales of individual fruits throughout the three months.

  • For Mango, enter the following formula using the SUMPRODUCT function.

=SUM(SUMPRODUCT('Jan'!B5:B14='Using SUMPRODUCT'!B5)*' Jan'!C5:C14),

SUMPRODUCT((' Feb'!B5:B14='Using SUMPRODUCT'!B5)*Feb!C5:C14),

SUMPRODUCT((Mar!B5:B14='Using SUMPRODUCT'!B5)*Mar!C5:C14))

Jan’!B5:B14  is the range of Fruit Names in the Jan sheet, Feb’!B5:B14  is the range of Fruit Names in the Feb sheet and Mar!B5:B14  is the range of Fruit Names in the Mar sheet.

  • SUMPRODUCT function with multiple criteria not workingPress ENTER and use the Fill Handle for the rest of the cells.

The output will be #REF!

SUMPRODUCT function with multiple criteria not working
This error is occurring because we have added Jan, Feb and Mar as references by using the Name Manager in the Formula bar.


The names of our reference sheets are Jan, Feb, and Mar as well.

SUMPRODUCT function with multiple criteria not working
What happens here is that the SUMPRODUCT function cannot understand the reference because we have added a Name Manager, which is the same as Sheet Names. So, Excel takes Jan, Feb and Mar as references from the Name Manager. This is the reason why we get the output #REF!

Solution:

To avoid this error, we must not use the same names as Sheet Names and as references in the Name Manager.

When we don’t include the same name reference as the sheet name through Name Manager, we’ll find the valid output as 210 for Mango and other outputs after using the Fill Handle.

Read More: How to Use SUMPRODUCT IF in Excel


Things to Remember

  • Non-numeric array entries will be treated as zeroes by the SUMPRODUCT function.
  • In Excel 2016, Excel 2013, Excel 2010, and Excel 2007, SUMPRODUCT supports up to 255 arguments, while prior Excel versions only supported 30 arguments.
  • Arrays will provide TRUE and FALSE values as a result of logical testing. Most of the time, it is best to convert them to 1s and 0s.
  • The SUMPRODUCT formula treats all the non-numeric values as zero.
  • The SUMPRODUCT formula will return an error if it is not double-negative signed or multiplied by one.

Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo