How to find total number of values in a number ran

问题:

For example:

DATA COLUMN: 23 TO 26, 40, 22, 7, 27 TO 28, 49, 50 TO 51, 48, 136, 169, 190 TO 192, 17 TO 22 AND NIL

*includes individual numbers, number range and word.

I have these datas as shown above in each individual cell, how do I come up with a formula that sums up all the total number of values?

I need to do some updating of datas.

I am currently using a formula provided by Jerry Beaucaire:

=ROWS(INDIRECT(SUBSTITUTE(SUBSTITUTE(A1," - ",":")," TO ", ":")))

Thanks!

回答1:

Try this formula based on JMAX's approach

=SUM(IF(ISERR(FIND("TO",A1:A6)),ISNUMBER(A1:A6)+0,MID(A1:A6,FIND("TO",A1:A6)+2,9)-LEFT(A1:A6,FIND("TO",A1:A6)-1)+1))

confirmed with CTRL+SHIFT+ENTER

assumes delimiter is "TO" as per your examples

Revised approach:

FIND is case-sensitive, SEARCH is not, so to allow "TO" or "to" you can change all instances of FIND to SEARCH. The following version will therefore cope with either of those, blank cells, cells with "Nil", cells with entries such as 23, 24, 27 (counted as 3) or 11 & 99 (counted as 2) or cells with a single number like 33 (counted as 1)

=SUM(IF(ISERR(SEARCH("TO",A1:A6)),IF((A1:A6="Nil")+(A1:A6=""),0,LEN(A1:A6)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A6,",",""),"&",""))+1),MID(A1:A6,SEARCH("TO",A1:A6)+2,9)-LEFT(A1:A6,SEARCH("TO",A1:A6)-1)+1))

Revision no 2

Assuming data in A1:A22

=SUM(IF(ISNUMBER(LEFT(A1:A22)+0),IF(ISNUMBER(SEARCH("to",A1:A22)),MID(A1:A22,SEARCH("TO",A1:A22)+2,9)-LEFT(A1:A22,SEARCH("TO",A1:A22)-1)+1,LEN(A1:A22)-LEN(SUBSTITUTE(SUBSTITUTE(A1:A22,",",""),"&",""))+1)))

回答2:

If you want to use only one cell to calculate the whole results, you can use this one (based on my solution because the INDIRECT wouldn't work there):

=SUMPRODUCT(IF(ISERR(FIND("-",A1:A6)),
MID(A1:A6,FIND("TO",A1:A6)+2,LEN(A1:A6))*1-LEFT(A1:A6,FIND("TO",A1:A6)-1)*1+1,
MID(A1:A6,FIND("-",A1:A6)+1,LEN(A1:A6))*1-LEFT(A1:A6,FIND("-",A1:A6)-1)*1+1)
)

回答3:

If you want a single formula to collect these values from a range of cells, I would write a new custom function for this. This is how i would do that...

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given below)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

Here's the UDF code for a new function called USERCOUNT()*

Option Explicit

Function USERCOUNT(RNG As Range) As Long
Dim cell As Range, Cnt As Long, c As Long, buf As String

    For Each cell In RNG
        If IsNumeric(cell) And cell > 0 Then
            Cnt = Cnt + 1
        ElseIf InStr(1, cell, ",") > 0 Or InStr(1, cell, "&") > 0 Then
            Cnt = Cnt + (Len(cell) - Len(Replace(Replace(cell, ",", ""), "&", "")) + 1)
        ElseIf InStr(1, cell, " TO ") > 0 Or InStr(1, cell, " - ") > 0 Then
            buf = Replace(Replace(cell, " TO ", ":"), " - ", ":")
            Cnt = Cnt + Rows(buf).Rows.Count
            buf = ""
        End If
    Next cell

    USERCOUNT = Cnt

End Function

That is simply used in a cell like a normal function referencing a range of cell: