# SUMPRODUCT fails because of #REF values

#### alphaexcel

##### Board Regular
The sumproduct I am using fails because items in the final range contain the #REF error. These items do not need to be included in the count in any case as they would never meet the criteria specified in the 2 rows previous, which the sumproduct bases the calculation on. Any ideas?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Mike Blackman

##### Well-known Member
Hi,

If the Array is not required then I would assume removing it would be best, Are you 100% sure what ever used to be there was not important?

Can you possibly post your code If removing the Array doesn't work for you?

#### iliace

##### Well-known Member
You have to use an array formula. Here is an example, look starting with the bold red:

Excel Workbook
ABCD
1160c
2255a
3389c
4471c
5551b
6686b
7773a390
8889a
99#REF!b
101071a
111175c
121289a
131384c
1414#REF!a
151568a
Sheet1

#### Jonmo1

##### MrExcel MVP
Perhaps you could correct the formulas in column B to NOT return the Ref Error...

=IF(ISERROR(orignalformula),"",originalformula)

#### barry houdini

##### MrExcel MVP
Any ideas?

Assuming your are summing column C based on criteria in columns A and B, column C may contain errors but not in rows where both criteria are met try something like

=SUM(IF(A1:A10="x",IF(B1:B10="y",C1:C10)))

confirmed with CTRL+SHIFT+ENTER

Last edited:

#### alphaexcel

##### Board Regular
Is there any way round this, other than using the Ctrl+Shift+Enter...i really dont like using these array formulae, hence the use of SUMPRODUCT.

Thanks

#### iliace

##### Well-known Member
Either fix the #REF errors, or if they carry some significance, create another column where you have a non-array version of what jonmo suggested, then do SUMPRODUCT on that column.

Replies
2
Views
296
Replies
14
Views
288
Replies
1
Views
108
Replies
0
Views
107
Replies
2
Views
250

1,190,638
Messages
5,982,073
Members
439,753
Latest member
mnyankee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back