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

If you are using the SUMPRODUCT function in Excel and facing problems while using it, this article is for you. The SUMPRODUCT function is handy to use but sometimes we may face problems while dealing with multiple criteria. In this article, we’ll discuss SUMPRODUCT with multiple criteria not working in Excel.

SUMPRODUCT with Multiple Criteria Not Working in Excel: 3 Solutions

The problems we face while using the SUMPRODUCT function with multiple criteria are solvable. We just need to identify those problems first and solve them accordingly.

1. If the Specified Arrays Are Not Same

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

Firstly, we have put the formula with the SUMPRODUCT function in the D17 cell like this.

Here, 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.

Secondly, press ENTER, and we’ll see that our output is #VALUE!.

Now, the main question is why this error has occurred.
Importantly, we always have to keep in mind that the ranges we use in the SUMPRODUCT function should be equal in Rows. Here, in the above picture, we have used C5:C15 as the first range, which has 11 Rows. On the other hand, we have used D5:D12 as the second range with 8 Rows. So, these two types of ranges don’t have an equal number of Rows. That’s why the SUMPRODUCT function has given an error as output.

Solution:

So, what is the solution to this error?

We have to take an equal number of Rows as ranges. In the picture below, we have written the formula in the D17 cell like this.

Here, the number of Rows of the two ranges is equal, and that is 11. 

So, eventually, when we press ENTER the output becomes valid and it is 3318.

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

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, then we’ll get a #VALUE! Error or #NAME? Error. Here, the text could be the result of a calculation from another formula. Another thing that makes it an error is 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. 

Firstly, we write the formula in the D17 cell with the SUMPRODUCT function like this.

Secondly, if we press ENTER, we’ll see the output as #Name!. So, the formula has faced errors.

Solution:

If we replace the text of the D9 cell with a General Number, we’ll get a valid output by using the same formula. Here, we have replaced the text with 12 and the output is 3318 like this.

Related Content: Excel SUMPRODUCT Function Based on Date Range

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, then 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.

The second dataset is the Sales in February.

The third dataset is the Sales in March.

Now, we want to find the Total Sales of individual fruits throughout the three months.
In the case of Mango, we have written the following formula using the SUMPRODUCT function.

Here, 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

Eventually, if we press ENTER, we’ll find the output as #REF! as well as in other cells after using the Fill Handle.

Why do we make errors, and what is the solution to them?
We have added Jan, Feb, and Mar as references by using the Name Manager in the Formulas bar.

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

So, 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. That’s why our output is #REF!

Solution:
If we want to avoid this error, we have to be careful when using Name Manager. 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 by 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

Conclusion

If we study this article properly, we can understand the common errors made while using the SUMPRODUCT function and the ways to avoid them.

Related Articles

• How to Use SUMPRODUCT with Criteria in Excel
• SUMPRODUCT Across Multiple Sheets in Excel
• SUMPRODUCT for Counting with Multiple Criteria in Excel
  
• How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel
• Excel SUMPRODUCT with Multiple Criteria in Same Column

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