How to sum number values, excluding data returned #N/A during Vloo

D

Dyer

My question is how due I sum all values in a column that have a numerical
value, and exclude the data in the same column returned #N/A. For example
the database hase the following data;
19.40
17.93
#N/A
2,002.41
322.82
#N/A
8.39

I need to sum over 3888 cells in one column to get a grand total of values,
not #N/A.
Thanks for the assistance.
 
B

Bob Phillips

=SUM(IF(ISNUMBER(A2:A20),A2:A20))

which is an array formula, so commit with Ctlr-Shift-Enter, not just Enter
 
D

Dave Peterson

One more:

=SUMIF(A:A,"<10E37")

10E37 is a huge number in scientific notation.
 

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

How to set a formula if the value is with #N/A 4
Sum Function When Formula Produces #N/A 2
unique entry sum 0
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row 5
Sum if unique and another criteria 0
Sum or Average with #N/A cells 5
Sum the results of a vlookup 6
How to ignore a column of numbers in Grand Total for rows 1

Top