text and numbers in same cell, sum of numbers ?

[skgurmeet]

Dear All,

i have query, please help out.

multiple numbers and text are in single cell which are separated through spaces or comma, is there any formula which can bypass text and add all the numbers in the cell.

For example:

<table style="width: 384px; height: 83px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 125pt;" width="167"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 125pt;" height="20" width="167">red 12, yellow 10, green 5</td> <td class="xl63" style="border-left: medium none; width: 48pt;" align="right" width="64">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">red 3, blue 9</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">yellow 21, blue 3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">24</td> </tr> </tbody></table>
Thanks,
Gurmeet

 

[FormR]

I don't understand the "0"& part or the --
--(LEFT(B2:H2,1)="D")): is checking if the cells have a "D" in position 1 (starting on the left) within the range of cells b2 to h2. Again I don't understand the --.

Hi, does this help with the understanding at all?

p.s. the 0& was an attempt to stop the formula failing if you have blank cells in the range.

Excel 2012
	A	B	C	D	E	F	G	H	I
2		N12	N12	N15	N12	N12	D12	D12	Comment
3	Step 1	12	12	15	12	12	12	12	< This is text as the MID function returns text
4	Step 2	12	12	15	12	12	12	12	< This performs a arithmetic operation to coerce the text values to number (+0 or *1 would also work)
5	Step 3	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	< These are Boolean values
6	Step 4	1	1	1	1	1	0	0	< This performs a arithmetic operation to coerce the Boolean values to 1 (TRUE) 0 (FALSE)
7	Step 5	12	12	15	12	12	0	0	< This is thePRODUCT of the two arrays
8	Step 6	63							< This is the SUM of the PRODUCT of the two arrays
Sheet1

Cell Formulas
Range	Formula
B3	=MID(B2,2,99)
B4	=--B3
B5	=LEFT(B2,1)="N"
B6	=--B5
B7	=B4*B6
B8	=SUM(B7:H7)

 

[Danadams2504]

That all helps a lot - thank you for taking the time to explain it to me. I love this forum but I have a fear of blindly copy/pasting sometimes!

Kind regards,

Dan

 

[FormR]

@Dan - Glad it helped and welcome to the Forum

 

[kbarry010]

can this formula be adjusted to =SUMNUMS(A1:A500) in order to get a final tally of all numbers within a range of column A while continuing to ignore letters?


...see quote below

 

[kbarry010]

A single cell function would be quite complex... you could use a basic UDF to do the same

Function SumNums(rngS As Range, Optional strDelim As String = " ") As Double
    Dim vNums As Variant, lngNum As Long
    vNums = Split(rngS, strDelim)
    For lngNum = LBound(vNums) To UBound(vNums) Step 1
        SumNums = SumNums + Val(vNums(lngNum))
    Next lngNum
End Function

Below we use space as delimiter as this precedes numbers in the strings

Sheet5

	A	B
1	red 12, yellow 10, green 5	27
2	red 3, blue 9	12
3	red	0
4	3	3

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

Spreadsheet Formulas

Cell Formula B1 =SUMNUMS(A1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

can this formula be adjusted to =SUMNUMS(A1:A500) in order to get a final tally of all numbers within a range of column A while continuing to ignore letters?

 

[FormR]

can this formula be adjusted to =SUMNUMS(A1:A500) in order to get a final tally of all numbers within a range of column A while continuing to ignore letters?

Hi, something like this:

Function SumNums(rngS As Range, Optional strDelim As String = " ") As Double
'http://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers-4.html
    Dim vNums As Variant, lngNum As Long, c As Range
    For Each c In rngS
    vNums = Split(c, strDelim)
        For lngNum = LBound(vNums) To UBound(vNums) Step 1
            SumNums = SumNums + Val(vNums(lngNum))
        Next lngNum
    Next c
End Function

Excel 2012
	A
1	red 12, yellow 10, green 5
2	red 3, blue 9
3	red
4	39
Sheet1

Cell Formulas
Range	Formula
A4	=SumNums(A1:A3)

 

[BarryHatlle]

Hi there,
i know this is an old thread but i have been went thru from page 1 and I've got no answer for my question, i hope the experts will help me out with my query. Many thanks in advance and i'm sorry if the question is abit stupid. Ok now lets get to the question.

i have a set of text in a cell with formula and the results are in numbers, so i would like to subtotal the result from that cell adding upto the next 3 cell, is that possible ?

this is a tracking sheet for the course that is attended by my team members

Iron Man (Total = 26 Hrs) <-- Here is the total hrs that i would like to archive
Dept Core = (Total = 24 Hrs) <- Formula [="Dept Core = (Total = "& (SUM(Y3:Y19)) & " Hrs)" ]	IEE = 2 Hrs	Organization Growth = 0 Hrs
-	1	-
24	-	-
-	-	-
-	-	-
-	-	-
-	1	-
-	-	-

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming you have shown columns Y:AA and there are similar formulas in Z2 and AA2, then this formula in Y1 should get you the result you want. If not, you may need to give more details.

="Iron Man (Total = "&SUM(Y3:AA19)&" Hrs)"

 

[BarryHatlle]

Dear Peter, many thanks for the prompt response, i appreciate it a lot. so basically i just have to use the same formula which i use for the dept core is it ? i though of using something like this : =SUMPRODUCT(--("0"&MID(Y2:AA2,2,99)),--(LEFT(Y2:AA2,1)="Total"))
but i could get the result and keeps showing #VALUE ! error.

thanks in advance

 

[Peter_SSs]

thanks in advance

Does that mean you still need help and that the direct method I suggested doesn't work for some reason?

If so, I would want to know why the direct method doesn't work, what your actual row 2 contains (from column Y) and what you were attempting with the below formula structure as it doesn't appear to relate to what is shown in post #37

i though of using something like this : =SUMPRODUCT(--("0"&MID(Y2:AA2,2,99)),--(LEFT(Y2:AA2,1)="Total"))