How to ignore #NA in subtotals?

[Guest]

I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).

Each table has a SUM total, but, if #NA exits in any one of the cells
totaled the
SUM is returned #NA. How can I overcome this?

 

[Bob Tarburton]

One way is to use
=if(isna(your_function),"N/A",your_function)
intead of allowing the #N/A

 

[Dave Peterson]

I usually use two columns when I do this--one for the real values and one to be
used for charting (with the #n/a's).

But you could do something like:

=sum(if(isnumber(a1:a30),a1:a30))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)