adding numbers ignoring txt

[jubszz]

Rite guys
I have the following numbers on my sheet. They are amounts in money and people initials. how do I just add up the numbers. if they were just numbers I wwould have just used A1:A6

825.90mh

352.70kf

650.22hj

952.25nk

354.02nm

758.25fd Any help

<colgroup><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> </colgroup><tbody>
</tbody>

 

[Peter_SSs]

i wasnt looking at the cell ranges i was using, after playing around with it. It now works fine

Ah, that's good news,

 

[jubszz]

peter,
although the solution you gave me works with my version of excel 2010, I have tried to use it on a 2003 version.
If I am correct, the function SUMPRODUCT does was not available till 2007 so this is were my problem now lies. So can you think of a solution that will work with excel 2003?

 

[Peter_SSs]

peter,
although the solution you gave me works with my version of excel 2010, I have tried to use it on a 2003 version.
If I am correct, the function SUMPRODUCT does was not available till 2007 so this is were my problem now lies. So can you think of a solution that will work with excel 2003?

SUMPRODUCT has been around for a long time & certainly from before 2003. The formula should work exactly the same in Excel 2003 as it does in 2010.

Below is my Excel 2003 sheet re-creating my earlier data/solution and it is working fine.

Have you changed the data or formula in some way?

Can you give some small sample data that is NOT working in Excel 2003, the formula you used on that sample data and what result that formula is currently giving you in Excel 2003?

Excel Workbook
	B
1
2	825.90mh
3	352.70kf
4	650.22hj
5	952.25nk
6	354.02nm
7	758.25fd
8
9	3893.34
Same formula using Excel 2003

 

[jubszz]

03/03/2013
825.90 MH
352.70 KS
1560.45 AB
18779.95 SP
839.75 JG
962.10 JA
743.10 JR
#VALUE! =SUMPRODUCT(--(LEFT(A4:A18,LEN(A4:A18)-2)))					#value! A value used in the formular is in the wrong data type

<colgroup><col style="mso-width-source:userset;mso-width-alt:6107;width:125pt" width="167"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:7680;width:158pt" width="210"> </colgroup><tbody>
</tbody>

 

[jubszz]

=SUMPRODUCT(--(LEFT(A4:A30,LEN(A4:A30)-2)))

this is how i moded the formular

 

[Peter_SSs]

As expected, the issue is not related to the version of Excel but to the fact there are blank cells among the data now. With that data my earlier suggestion will not work in Excel 2010 either.
Try this instead. Adjust range to suit your data.
Note that this formula assumes a space between the numbers and initials per your current data sample and will also allow initials of length other than 2.
However, it could not be relied upon to give correct results if it is possible that numbers exist in the data with no initials at all.

Excel Workbook
	A
4	825.90 MH
5	352.70 KS
6
7
8
9
10
11
12
13	1560.45 AB
14	18779.95 SP
15
16	839.75 JG
17	962.10 JA
18	743.10 JR
19
20	24063.95
Sum without initials

 

[jubszz]

As ever Peter,

you are a star

Thank you

As expected, the issue is not related to the version of Excel but to the fact there are blank cells among the data now. With that data my earlier suggestion will not work in Excel 2010 either.
Try this instead. Adjust range to suit your data.
Note that this formula assumes a space between the numbers and initials per your current data sample and will also allow initials of length other than 2.
However, it could not be relied upon to give correct results if it is possible that numbers exist in the data with no initials at all.

Sum without initials

*	A
4	825.90 MH
5	352.70 KS
6	*
7	*
8	*
9	*
10	*
11	*
12	*
13	1560.45 AB
14	18779.95 SP
15	*
16	839.75 JG
17	962.10 JA
18	743.10 JR
19	*
20	24063.95

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A20 =SUMPRODUCT(--LEFT(A4:A18&0,FIND(" ",A4:A18&" "))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Fucius]

Hi guys,

I know this is a super old thread but was trying to find a solution to add number while ignoring text also. I tried the =SUMPRODUCT solution but I either get a #value error or "0" as my answer. What I'd like to be able to do is add selected cells together within the spread sheet. ie:

Sep-2016
29	5	12	19
	2*	2*N
	2*	2*N

<tbody>
</tbody>

Sep-2016
29	5	12	19
	4*	4*	4*
	6*	6*	6*
	6*	6*	6*

<tbody>
</tbody>

So for the above I’d like to be able to add ONLY the numbers ignoring the “*” and “*N” for the column week of Sep 12^th which would be selecting cells: 2*N+2*N+4*+6*+6* giving me a total of 20.

Any help would be greatly appreciated!

 

[Peter_SSs]

Something like this? Formula in B16, copied across.

Excel 2010 32 bit
	A	B	C	D
2	Sep-16
3	29	5	12	19
4
5		2*	2*N
6		2*	2*N
7
8
9	Sep-16
10	29	5	12	19
11
12		4*	4*	4*
13		6*	6*	6*
14		6*	6*	6*
15
16		20	20	16
Sum Ignore Text

Cell Formulas
Range	Formula
B16	=SUMPRODUCT(--ISTEXT(B3:B14),--(LEFT(0&B3:B14,FIND("*",0&B3:B14&"*")-1)))