# SUMPRODUCT Returns N/A Error

• Hi all -

I have sumproduct formula that returns #N/A error and I'm not sure why
=SUMPRODUCT((YTD!\$A\$2:\$A\$2599=B\$10)*(YTD!\$P\$2:\$P\$2599=\$Q19)*(YTD!\$E\$2:\$E\$2599))

If I narrow the range from \$939 to \$1093 then the formula works.
But that is only evaluating 1 unit of 10
As I add worksheets to the workbook and update the unit numbers in B10, the entire range needs to be evaluated for matching criteria.

Any ideas?
Sorry, workbook is way too large to upload.
TIA
-markc

• Re: Array Formula Returns Error

Have you entered this as an array formula? It should not be. If you haven't the title should be changed because it is misleading.

• Re: Array Formula Returns Error

Thanks
It is not committed with (ctrl+shift+enter)
How can I change the title?

Thanks
-markc

• Re: Array Formula Returns Error

Hi

Do you have any error values in any of your ranges? Specifically #N/A error values.

• Re: SUMPRODUCT Formula Returns Error

Thanks parsnip
No.
I scrolled through all data and used Edit..Go To...Special...Formulas...Errors
"No Errors Were Found"

There are no blank cells in the range.
Thanks
-markc

• Re: SUMPRODUCT Formula Returns Error

Try substituting the formula with an array formula (Ctrl+Shift+enter):

=SUM(IF(YTD!\$A\$2:\$A\$2599=B\$10,IF(YTD!\$P\$2:\$P\$2599=\$Q19,YTD!\$E\$2:\$E\$2599)))

If this still errors then check the E column with:

=SUMIF(E2:E2599,"#N/A")

Richard

• Re: SUMPRODUCT Formula Returns Error

Thanks
The array formula does return the expected result.
Still curious why the sumproduct does not work though?

Thanks
-markc

• Re: SUMPRODUCT Formula Returns Error

Mark

If the array formula works then you must have an error value in the ranges A2:A2599, P2:P2599 or E2:E2599.

Richard

• Re: SUMPRODUCT Formula Returns Error

Thanks
Checked again
There is #N/A in col P
I guess it did not show in the Go To Special....Error because at some point I pasted the lookup formula as a value