Need help with having excel 'count #s' with a twist

[skonski]

Hey Guys,

I'm trying to have excel count numbers and ranges of numbers all within one cell. I'm aware of =Len(A1) etc but that gives me a character count which isn't helpful.

So for example:


1, 3–5, 14–18, 20–26, 29, 30, 33, and 34


are all in one cell. I'd like excel to count the digits within the ranges as well as the individual numbers. So i'd like the function to calculate "20" in this case.


Any way to do this?

Thanks in advance.

Ryan

 

[Eric W]

Welcome to the forum.

Yes, it is possible, but it's highly susceptible to variations in how the data is entered. Here are two ways:

	D	E	F	G
1	1, 3–5, 14–18, 20–26, 29, 30, 33, and 34
2	,1,3–5,14–18,20–26,29,30,33,34,	1 ,3–5 ,14–18 ,20–26 ,29 ,30 ,33 ,and 34
3	5	15	20	20

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D2 =","&SUBSTITUTE(SUBSTITUTE(D1," ",""),"and","")&"," E2 =SUBSTITUTE(D1,", "," ,") F3 =D3+E3 G3 =countnumbers(D1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D3 {=SUM(IF(ISNUMBER(FIND(","&ROW(1:100)&",",D2)),1))} E3 {=SUM(IFERROR(MID(E2,FIND(","&ROW(1:100)&"–",E2)+LEN(ROW(1:100))+2,3)-ROW(1:100)+1,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Using just native Excel functions, I managed to get the proper answer. I used helper cells D2:E3, and the final sum is in F3. Yes, they can all be combined into a single formula, but it would be very long. It would also need a little cleaning up to make it a bit more robust, but as a proof of concept it shows that it's possible.

Option 2 would be to use a UDF. Press Alt-F11 to open the VBA editor, from the menu select Insert > Module, and paste this code:

Public Function CountNumbers(ByVal MyList As String) As Long
Dim MyNums As Variant, x As Variant, wk As Variant

    MyList = Replace(MyList, " ", "")
    MyList = Replace(MyList, "and", "")
    MyList = Replace(MyList, ChrW(8211), "-")
    MyNums = Split(MyList, ",")
    
    For Each x In MyNums
        wk = Split(x, "-")
        If UBound(wk) = 0 Then
            CountNumbers = CountNumbers + 1
        Else
            CountNumbers = wk(1) - wk(0) + 1 + CountNumbers
        End If
    Next x
            
End Function

Then press Alt-Q to exit the editor, and use the formula in G3. Much neater. This is also prone to unexpected formatted data, but it's much better than the native formulas.

Let me know if this helps.