how can I ignore or hide the #N/A when doing a sum or subtotal?

[Jules73]

I have a list of products on drop downs (validation) which also populates a
cell next to it with its base cost. However if nothing is selected it shows
as #N/A. At the bottom of the list is a subtotal which works if it only looks
at cells with numbers. If it see an #N/A it doesnt total the ones that have
been populated. How can I get the subtotal to ignore blanks or #N/A? How do I
get it to show Blanks in stead of #N/A for that matter?

Thanks in advance.

 

[Toppers]

=SUMIF(I1:I5,"<>#N/A")

To remove #N/A you need something like:

=IF(ISNA(myformula),"",myformula)

HTH

 

[PWK]

I have a list of products on drop downs (validation) which also populates a
cell next to it with its base cost. However if nothing is selected it shows
as #N/A. At the bottom of the list is a subtotal which works if it only looks
at cells with numbers. If it see an #N/A it doesnt total the ones that have
been populated. How can I get the subtotal to ignore blanks or #N/A? How do I
get it to show Blanks in stead of #N/A for that matter?

Thanks in advance.

I had the same problem. You need to use ISERROR in your formula.

 

[Billy Liddel]

I have a list of products on drop downs (validation) which also populates a
cell next to it with its base cost. However if nothing is selected it shows
as #N/A. At the bottom of the list is a subtotal which works if it only looks
at cells with numbers. If it see an #N/A it doesnt total the ones that have
been populated. How can I get the subtotal to ignore blanks or #N/A? How do I
get it to show Blanks in stead of #N/A for that matter?

Jules
I'm not sure if this will do but this UDF will ignore errors and sum the range

Function SumErrRange(range) As Double
Dim c, myTotal As Double
For Each c In range
If IsNumeric(c) Then
myTotal = myTotal + c
End If
Next
SumErr = myTotal
End Function

Hope this works for you
Peter

 

[Gord Dibben]

Which hides all errors, not just #N/A

It is better to trap the error at the source rather than deal with it later.

See Toppers' reply.


Gord Dibben MS Excel MVP