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.

**Table of Contents**Expand

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

`=SUMPRODUCT(C5:C15,D5:D12)`

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.

`=SUMPRODUCT(C5:C15,D5:D15)`

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.

`=SUMPRODUCT(C5:C15,D5:D15)`

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

`=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))`

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**