#VALUE! and #N/A return as 0

**cheddarthief** · 04-12-2010, 04:02 PM

Is there any way to use a macro that returns a value of "0" in place of both #VALUE! and #N/A? I ask because I have a summing function that cannot work when either of these are in the summation. The only way to make it work is to delete the rows. Any ideas?

Jim

**daddylonglegs** · 04-12-2010, 04:06 PM

You can use this formula to sum a range and ignore any error values

=SUM(SUMIF(A1:A100,{"<0",">0"}))

**zbor** · 04-12-2010, 04:14 PM

How about this:

=SUMIF(B:B, "<="&9.999999E+306)

**cheddarthief** · 04-12-2010, 04:21 PM

That worked great for the summing portion. But what if the cell equation looks as follows and you wanted to return a value of 0?:

Please Login or Register  to view this content.

or

What if summing not a range but rather individual cells? The following happens to be a cell that when I delete on of the summation cells (L53), returns a value of #REF!.

Please Login or Register  to view this content.

Jim

**zbor** · 04-13-2010, 03:05 AM

First solution would be to avoid errors. We would need more info (best would be example workbook) to see what cause and how avoid those errors.

For product you can use this:

=IF(ISERROR(C22*P22*Q22), 0, C22*P22*Q22)

For SUM with non-series cells I would need more time...

**cheddarthief** · 04-13-2010, 10:42 AM

Any help with errors when either adding particular cells or multiply particular cells? I'm sure there is a variant to the above formula but I can't figure it out.

Jim