MS Excel count Range in single cell

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
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

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

rusa31

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,301
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
 

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
Joined
May 26, 2009
Messages
17,419
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
Joined
May 26, 2009
Messages
17,419
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
You are welcome - thanks for the reply.
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. :)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,419
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top