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.

**Download Practice Workbook**

**3 Solutions If SUMPRODUCT with Multiple Criteria Is Not Working in Excel**

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:** **SUMPRODUCT Multiple Criteria Same Column (3 Useful W**

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

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

**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, **the SUMPRODUCT function** can not 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: ****SUMPRODUCT Across Multiple Sheets in Excel (2 Methods)**

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

**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. Please feel free to visit our official Excel learning platform, **ExcelDemy, **for further queries.