Sum column range event thou #N/a appears

J

Jimbola

Hi,

I have a column range, A1:A40 with formulas in it, sometimes the formula
produces the #N/A error, which is fine. But is there a way to sum the range
so that it counts all the numbers and ignores the #N/A.

Thanks
J
 
B

Bob Phillips

J,

As it's only #N/A, you can use

=SUM(IF(1-ISNA(A1:A40),IF(A1:A40>0,A1:A40)))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
F

Frank Kabel

Hi
in addition to Bob#s and Aladin's suggestions why not prevent the #NA
errors. e.g. with something like
=IF(ISNA(Your_formula),"",your_formula)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Columns in Sum Range using SUMIFS 1
Sumif apllying to all sheets in workbook 2
Sum every Nth Row (almost working) 2
need formula to add two different criteria in a range 6
Count Number of Unique Cells That Don't Have Certain Characters 0
Find #N/A in column D 2
Sum Function When Formula Produces #N/A 2
Compute Additional Hours Needed Using "if" or "lookup" 1

Top