SUMPRODUCT fails because of #REF values

[alphaexcel]

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?

 

[Mike Blackman]

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]

You have to use an array formula. Here is an example, look starting with the bold red:

Excel Workbook
	A	B	C	D
1	1	60	c
2	2	55	a
3	3	89	c
4	4	71	c
5	5	51	b
6	6	86	b
7	7	73	a	390
8	8	89	a
9	9	#REF!	b
10	10	71	a
11	11	75	c
12	12	89	a
13	13	84	c
14	14	#REF!	a
15	15	68	a
Sheet1

 

[Jonmo1]

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

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

 

[barry houdini]

Any ideas?

What's your current formula?

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

 

[alphaexcel]

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]

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.