How to SUM if the cells contain numbers & text

[earp13]

Hi everyone,

Hopefully this is a straight forward one?!
I've been trawling through lots of forums for a simple answer to this but can't seem to find one.

I want to be able to do a SUM total of a list of numbers, however my list contains text in each cell and looks like this:

2 (Bob)
5 (Dave)
18 (Sharon)
6 (Trudy)

Is there a simple way to do a SUM formula at the bottom of this which just gives me the total SUM of the numbers.

I've seen some people saying that doing =SUM(A1:A4) will leave out text but this is only if one cell has a number and one cell has text, not if the cells have both a number and text in the same cell.
When I try to use this formula on my current list the number reads "0".

Any & all help welcome!

[AlKey]

Try this

Please Login or Register  to view this content.

v	A	B	C
1	2 (Bob)		31
2	5 (Dave)
3	18 (Sharon)
4	6 (Trudy)
5

[MarvinP]

Hi earp13 and welcome to the forum,

Why you would ever want to do this is unknown. You should really pull the numbers out to a new column and add zero to them (so they are numbers) and then add them. BUT here is an answer for you. It is an array formula so needs to be entered with the Ctrl and Shift keys down before you press the enter key. See CSE or Array formulas in excel.

Please Login or Register  to view this content.

Array formula to add left numbers.xlsx

[earp13]

It works!
Thanks for this.
Just so I'm able to learn something about this process what is the significance of the last part of the formula (" ",A2)))

Also, I've noticed that if you have some cells in the list which are blank it gives a #Value! is there a way to stop it from doing this?

[Phuocam]

Try this ...

=SUM(IFERROR(--LEFT(A1:A4,FIND(" ",A1:A4&" ")-1),""))

Enter with Ctrl+Shift+Enter.

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Name		Total
2	2 (Bob)		41
3	5 (Dave)
4	18 (Sharon)
5	6 (Trudy)
6
7	10 (Biff)

This array formula** entered in C2:

=SUM(IF(ISNUMBER(--LEFT(A1:A10)),--LEFT(A1:A10,FIND(" ",A1:A10)-1)))

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