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

[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.

 

[T. Valko]

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.

 

[Jacob Skaria]

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

 

[Ron Rosenfeld]

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.

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

--ron

 

[NCCADM]

Thanks for the solutions guys

I'm now off to study arrays