# MS Excel count Range in single cell

#### rusa31

##### New Member
Hello,

Im doing employee "Date On-Leave" data in excel. There are more than 10 employee that I have to key-in for every month.
so im planning to create a formula to make my work more effective and also to avoid human mistake. I need help on the for formula that can give Total count of multiple range number in single cell.

Attached picture:- Info :-

1) Calculate Range in single cell
2) Every range number separated by comma ","
3) could be more than one comma ","

Note : Sorry. I cannot change the table design. My bos wanted this way. Really appreciate for the help.

Thank you.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### rusa31

##### New Member
Hello,

Im doing employee "Date On-Leave" data in excel. There are more than 10 employee that I have to key-in for every month.
so im planning to create a formula to make my work more effective and also to avoid human mistake. I need help on the for formula that can give Total count of multiple range number in single cell.

Attached picture. Info :-

1) Calculate Range in single cell
2) Every range number separated by comma ","
3) could be more than one comma ","
4) Could be combination of single number and range

Note : Sorry. I cannot change the table design. My bos wanted this way. Really appreciate for the help.

Thank you.

Last edited by a moderator:

#### mumps

##### Well-known Member
Assuming your numbers are in column B starting in row 2 and you want the totals in column C, try this macro:
VBA Code:
``````Sub GetTotal()
Application.ScreenUpdating = False
Dim LastRow As Long, rng As Range, v1 As Variant, v2 As Variant, i As Long, ii As Long, x As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In Range("B2:B" & LastRow)
v1 = Split(rng, ",")
For i = LBound(v1) To UBound(v1)
If InStr(v1(i), "-") > 0 Then
v2 = Split(v1(i), "-")
For ii = v2(0) To v2(1)
x = x + 1
Next ii
Else
x = x + 1
End If
Next i
rng.Offset(, 1) = x
x = 0
Next rng
Application.ScreenUpdating = True
End Sub``````

• anthonyexcel

#### rusa31

##### New Member
Assuming your numbers are in column B starting in row 2 and you want the totals in column C, try this macro:
VBA Code:
``````Sub GetTotal()
Application.ScreenUpdating = False
Dim LastRow As Long, rng As Range, v1 As Variant, v2 As Variant, i As Long, ii As Long, x As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In Range("B2:B" & LastRow)
v1 = Split(rng, ",")
For i = LBound(v1) To UBound(v1)
If InStr(v1(i), "-") > 0 Then
v2 = Split(v1(i), "-")
For ii = v2(0) To v2(1)
x = x + 1
Next ii
Else
x = x + 1
End If
Next i
rng.Offset(, 1) = x
x = 0
Next rng
Application.ScreenUpdating = True
End Sub``````

Hi Mumps,

Thank you for helping.

I've tried use the code given.

How ever i got the error Type Mismatch.
Im using Format Cell "Text".

Thank you. #### JoeMo

##### MrExcel MVP

Here's a UDF you can try. Use it like a worksheet function as shown in the example below after you install it.
NOTE: Assumes input data has comma followed by space delimiters.
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Book1
AB
218-23, 25-27, 29-3011
35, 13-165
413-16, 21, 306
54, 7, 103
661
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=CountDays(A2)

VBA Code:
``````Function CountDays(S As String) As Long
Dim sArr As Variant, i As Long
If S = "" Then
CountDays = 0
Exit Function
End If
If InStr(S, ",") = 0 Then
CountDays = 1
Exit Function
Else
sArr = Split(S, ", ")
For i = 0 To UBound(sArr)
If InStr(sArr(i), "-") > 0 Then
CountDays = CountDays + Split(sArr(i), "-")(1) - Split(sArr(i), "-")(0) + 1
Else
CountDays = CountDays + 1
End If
Next i
End If
End Function``````

• rusa31 and anthonyexcel

#### rusa31

##### New Member
Here's a UDF you can try. Use it like a worksheet function as shown in the example below after you install it.
NOTE: Assumes input data has comma followed by space delimiters.
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Book1
AB
218-23, 25-27, 29-3011
35, 13-165
413-16, 21, 306
54, 7, 103
661
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=CountDays(A2)

VBA Code:
``````Function CountDays(S As String) As Long
Dim sArr As Variant, i As Long
If S = "" Then
CountDays = 0
Exit Function
End If
If InStr(S, ",") = 0 Then
CountDays = 1
Exit Function
Else
sArr = Split(S, ", ")
For i = 0 To UBound(sArr)
If InStr(sArr(i), "-") > 0 Then
CountDays = CountDays + Split(sArr(i), "-")(1) - Split(sArr(i), "-")(0) + 1
Else
CountDays = CountDays + 1
End If
Next i
End If
End Function``````

Wow. It Works!!..Thank You JoeMo. Really appreciate.

#### JoeMo

##### MrExcel MVP

Wow. It Works!!..Thank You JoeMo. Really appreciate.
You are welcome - thanks for the reply.

#### rusa31

##### New Member
You are welcome - thanks for the reply.
Hi Joe,

Sorry.
I've another question.
what if the employee take Half-day leave. Example : On date 15th. the employee took half-day leave. with the symbol * It will count as 0.5.

I've tried to do workaround on your code u provided. but not successful.

VBA Code:
``````Function CountDays(S As String) As Long
Dim sArr As Variant, i As Long
If S = "" Then
CountDays = 0
Exit Function
End If

If InStr(S, ",") = 0 Then
CountDays = 1
Exit Function

[B]ElseIf InStr(S, "*,") = 0 Then
CountDays = 0.5
Exit Function[/B]

Else
sArr = Split(S, ", ")
For i = 0 To UBound(sArr)
If InStr(sArr(i), "-") > 0 Then
CountDays = CountDays + Split(sArr(i), "-")(1) - Split(sArr(i), "-")(0) + 1
Else
CountDays = CountDays + 1
End If
Next i
End If
End Function``````

appreciate if you can guide me.

Thank you. #### JoeMo

##### MrExcel MVP
OK, assuming "*" indicates a half day:
Book1 (version 1).xlsb
AB
212-13, 15*, 21-2810.5
312-13, 15*, 21*-2810
412-13*, 15*, 21*-289.5
512-13, 15, 21-2811
Sheet1
Cell Formulas
RangeFormula
B2:B5B2=countdays(A2)

VBA Code:
``````Function CountDays(S As String) As Double
Const Half As Double = 0.5
Dim Deduct As Double
Dim sArr As Variant, i As Long
If S = "" Then
CountDays = 0
Exit Function
End If
If InStr(S, "*") > 0 Then
For i = 1 To Len(S)
If Mid(S, i, 1) = "*" Then Deduct = Deduct + Half
Next i
S = Replace(S, "*", "")
End If
If InStr(S, ",") = 0 Then
CountDays = 1
Exit Function
Else
sArr = Split(S, ", ")
For i = 0 To UBound(sArr)
If InStr(sArr(i), "-") > 0 Then
CountDays = CountDays + Split(sArr(i), "-")(1) - Split(sArr(i), "-")(0) + 1
Else
CountDays = CountDays + 1
End If
Next i
End If
CountDays = CountDays - Deduct
End Function``````

• rusa31

#### rusa31

##### New Member
OK, assuming "*" indicates a half day:
Book1 (version 1).xlsb
AB
212-13, 15*, 21-2810.5
312-13, 15*, 21*-2810
412-13*, 15*, 21*-289.5
512-13, 15, 21-2811
Sheet1
Cell Formulas
RangeFormula
B2:B5B2=countdays(A2)

VBA Code:
``````Function CountDays(S As String) As Double
Const Half As Double = 0.5
Dim Deduct As Double
Dim sArr As Variant, i As Long
If S = "" Then
CountDays = 0
Exit Function
End If
If InStr(S, "*") > 0 Then
For i = 1 To Len(S)
If Mid(S, i, 1) = "*" Then Deduct = Deduct + Half
Next i
S = Replace(S, "*", "")
End If
If InStr(S, ",") = 0 Then
CountDays = 1
Exit Function
Else
sArr = Split(S, ", ")
For i = 0 To UBound(sArr)
If InStr(sArr(i), "-") > 0 Then
CountDays = CountDays + Split(sArr(i), "-")(1) - Split(sArr(i), "-")(0) + 1
Else
CountDays = CountDays + 1
End If
Next i
End If
CountDays = CountDays - Deduct
End Function``````
Thank you Joe.
Your are awesome. Replies
7
Views
102
Replies
3
Views
114
Replies
1
Views
133
Replies
0
Views
119
Replies
1
Views
149

### Forum statistics

1,129,475
Messages
5,636,545
Members
416,923
Latest member
jarri ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back