Empty cells

[Harry Limey]

I have a column of cells, some which have data (an amount of cash such as
£2.50) and some which have the symbol #NA because no data has yet been
entered.

What I would like to do is add up the column showing the total cash so far
accrued! but I cannot get the autosum function to work whilst some of the
cells are still showing the #NA!!

Is there a way round this please, should I be using something other than
autosum?

 

[Bernard Liengme]

Try =SUMIF(A1:A6,">0")
If you have both positive and negative numbers use:
=SUMIF(A1:A6,">0")+SUMIF(A1:A6,"<0")


BY the way SUM is called the SUM function; 'autosum' is the tool one can use
to enter the SUM (or other functions like AVERAGE) into a cell.
best wishes

 

[Earl Kiosterud]

Harry,

This might work for you:

=SUM(IF(ISERROR(A2:A5),0,A2:A5))

It's an array formula -- commit with Ctrl-Shift-Enter, not just Enter.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Harry Limey]

Bernard's solution worked just fine for me so I did not try any others,
thanks for the help!