Some cells contain # and text, some # only - want to sum # (number

  • Thread starter Thread starter NCCADM
  • Start date Start date
N

NCCADM

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.
 
Assuming that every cell that contains "*" also contains a number...

Try this array formula** :

=SUM(IF(RIGHT(A1:A10)="*",--LEFT(A1:A10,LEN(A1:A10)-1)),A1:A10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
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
 
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.
Click to expand...


=SUMPRODUCT(--SUBSTITUTE("0"&A1:A10,"*",""))

--ron
 
Back
Top