Counting time increments based on the number of times a text appears

Occasional Visitor

Hi there. I work at a company that meets with clients. We use their initials on the schedule.


Goal: Count the number of hours a client is scheduled.

Progress: I am able to count hours fairly effectively using the formulas =COUNTIF(B3:D3,"*AB*") or =SUMPRODUCT(--EXACT(B3:D3,$G$2)).

Issue: Some clients meet for 20 or 30 minute increments, and this is depicted in their cells using a slash symbol, where 1 slash mean 30 minutes and 2 slashes mean 20 minutes.

Question: How can I get an automatic count that considers the slashes as 30 minute increments (for 1 slash) or 20 minute increments (for 2 slashes), as shown in the first example below:

Even Better Schedule Example.png

If the second example is possible, that'd be great, but I'm sure it's a lot more complex, so I'm not expecting a solution to it. Thanks in advance!

2 Replies


Sub customer()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim z As Double

For i = 3 To 6
For k = 7 To 9
For j = 2 To 4

If InStr(1, Cells(i, j), Cells(2, k), vbTextCompare) Then

Select Case Len(Cells(i, j)) - Len(Replace(Cells(i, j), "/", ""))

Case Is = 0
z = z + 1

Case Is = 1
z = z + 0.5

Case Is = 2
z = z + 0.33

End Select

End If

Next j

Cells(i, k).Value = z
z = 0

Next k

Next i

End Sub

Maybe with these lines of code. You can click the button in cell L2 in the attached file to start the macro.


Please see attached workbook for a formula solution.