MS Excel count Range in single cell

R

rusa31

New Member
Joined
Jan 31, 2021
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:-

1612108157457.png


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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hello,

sorry .Additional info

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.
Capture.PNG

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:
Upvote 0
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
 
Upvote 0
mumps said:
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
Click to expand...

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. :)
 
Upvote 0
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
 
Upvote 0
JoeMo said:
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
Click to expand...

Wow. It Works!!..Thank You JoeMo. :)
Really appreciate.
 
Upvote 0
JoeMo said:
You are welcome - thanks for the reply.
Click to expand...
Hi Joe,

Sorry.
I've another question.
what if the employee take Half-day leave.

1612341426381.png


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. :)
 
Upvote 0
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
 
Upvote 0
JoeMo said:
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
Click to expand...
Thank you Joe.
Your are awesome. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
406
KRice
K
R
  • Question
Best approach (VBA) to search through a sheet for all matches to a selected range (ie. multiple cells)?
Replies
2
Views
47
radiocam
R
C
  • Question
Colouring cells based on contains value
Replies
2
Views
133
CawstonT
C
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
260
ashleighbrown
A
L
  • Question
count how many text strings in a range match full list of text strings and multiply by another data column
Replies
1
Views
211
lukaszzyla
L

Forum statistics

Threads
1,214,606
Messages
6,120,488
Members
448,967
Latest member
visheshkotha

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top