Sumproduct produces #N/A

I have 1200 rows of data and I am trying to sum up the quantity of defects for month/year using SUMPRODUCT; works great. When I alter the formula to incorporate 25000 rows of data to prevent from manual editing down the road when we add additional data the result changes to #N/A

Is there another method to do, or something I am missing?

Please Login or Register  to view this content.

Thank you so much.

Are there any #N/A errors within any of the referenced ranges ?
You'ld have to eliminate those.

Originally Posted by mcmahobt

If you're worried about dynamic ranges and not wanting to have to worry about changing the ranges manually, you could always make your data table an Excel Table by highlighting the data range, press CTRL+T, and insert your formula there. Your SUMPRODUCT formula will then autoadjust to take into account the new data entered.

That said, what is the result of the data in column I? Is it text or numerical data? You may want to upload a sample workbook too.

Thanks for your reply, Column M and P are the SUMPRODUCT columns I want to extend to 25000 rows.

Sample - SUMPRODUCT Issue.xlsx

Originally Posted by Jonmo1

Are there any #N/A errors within any of the referenced ranges ?
You'ld have to eliminate those.

Thanks for your reply, I attached the workbook in my last post, I didn't seem to find any #N/A errors.

You have #N/A error values in column I (I552 I554 & I558)
Clear those errors and the sumproduct will be fine.

Originally Posted by Jonmo1

You have #N/A error values in column I (I552 I554 & I558)
Clear those errors and the sumproduct will be fine.

Of course I do My bad. Thank so much for everyone's help

You're welcome.

FYI, you can use the SpecialCells function to help find errors (much easier than looking for them with your eyes)
Highlight the range of cells and press CTRL + G, Click special
There are alot of options in there that can hlep you find certain types of cell values.