To sum all cells; try the below formula

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=SUM(IF(A1:A20<"",SUBSTITUTE(A1:A20,"*","")+0))

If this post helps click Yes
---------------
Jacob Skaria


"NCCADM" wrote:

Say cell A1 contains 1*, and A2 contains 1, when I do sum(A1:A2) I get 1 as
the answer (obviously).

I would like to be able to include the numerical part of the entries in
these cells and ignore the text part. I know that I can format cell A1 like
this: # "*", but to apply a special format to each cell that includes the *
is a clumsy approach I think.

Is there any way to format all the cells so that the *'s are ignored,
without writing a macro?

Thanks for your time.