Excel: Dynamic counter for qualitative data

Question

I have created a workbook that will be used to schedule my team for different job functions on a weekly basis. The row headers are each person's name, and the column headers are time intervals (however some columns are by half hour and some are by the full hour. Ex: 8:30 | 9:30 | 10:00 | 11:00). Each cell features a dropdown of about 15 job functions.

I wish to create a counter for each job function related to each team member so that it counts how many hours a person was scheduled for a certain function.

I know how to use the COUNTIF function for this purpose. One cell for example, would be:

=0.5*COUNTIF([@[8:30am]],"coffee")+0.5*COUNTIF([@[9:00am]],"coffee")+0.5*COUNTIF([@[9:30am]],"coffee")+COUNTIF([@[10:00am]],"coffee")+COUNTIF([@[11:00am]],"coffee")+COUNTIF([@[12:00pm]],"coffee")+COUNTIF([@[1:00pm]],"coffee")+COUNTIF([@[2:00pm]],"coffee")+COUNTIF([@[3:00pm]],"coffee")+0.5*COUNTIF([@[4:00pm]],"coffee")

And this formula would have to be even longer, because I want the cell to count how many times "coffee" comes up for Person A in the entire week. The table has 5 sections of rows for each weekday.

BUT, what I really need is some way to make that counter dynamic. The schedule changes every week.... how can I make the counter continue to add values in a new week when I've cleared the old week data? Or is there a way I can link the table to another sheet in the workbook (making the separate table the master one) that totals the numbers found in the original counter table every week?

I welcome any suggestions or help! Thanks in advance

If you could post an image with sample data, input and result would be great. — Sgdva, Jul 11 '16 at 13:40
Additionally, if I need to use another platform for this requirement (like Access) I can do so. I just don't know what to do — SRag, Jul 11 '16 at 14:05

score 1 · Accepted Answer · answered Jul 11 '16 at 15:32

1

This question can be easily done with a change to the original worksheet provided in your question, if you make every column worth an hour or half an hour, then you can easily count these up through some simple code -

Sub Timesheet()
Dim wrk As Range
Dim i As Range
Dim Coffee As Range
Dim PaperWork As Range

    Set wrk = Sheets("Sheet1").Range("B2:I6")

    For Each i In wrk

    Set Coffee = Range("K" & i.Row)
    Set PaperWork = Range("L" & i.Row)

        If i = "Coffee" Then
            Coffee.Value = Coffee.Value + 1
        ElseIf i.Value = "PaperWork" Then
            PaperWork.Value = PaperWork.Value + 1
        Else

        End If

    Next
End Sub

These can easily be changed to fit your example, you would need to just change the if sections ("Coffee" and "PaperWork") to be equal to "coffee" or "Paperwork" etc, wrk's range value would need to be changed to the area of mondays times i.e. E10:V27

Please note this will only work if you change these to the every column being equal to an hour or halfanhour (where instead of the if statement adding 1, you'd add 0.5).

Hope this helps

answered Jul 11 '16 at 15:32

dyslexicgruffalo

364
1
4
18

Hi @lewisthegruffalo, thanks for your suggestion! However I really want to avoid changing every column to be the same value (ie 1hr or 1/2hr). My end user needs for the columns to be displayed as 6:30am | 7am | 7:30am | 8am | 8:30am | 9am | 10am | 11am | 12pm | 1pm | 2pm | 3pm | 3:30pm | 4pm | 4:30pm | 5pm | 5:30pm | 6pm | 6:30pm. Any way to implement your solution knowing this? – SRag Jul 11 '16 at 17:58
hmm, maybe including a nested if statement saying `If i.column >= 5 and i.column <=11 Then Coffee.Value = Coffee.Value = 0.5` (this means if the column is within an E-K you could then condinue on this if statement with`ElseIf` to do the other times. Hope this helps! If you want me to adapt my original answer I shall but I am busy atm so may take some time! – dyslexicgruffalo Jul 12 '16 at 07:55
Hi @lewisthegruffalo, thanks for this solution. I had a follow up question- I have the table set up so you can select more than one item from the dropdown list of possible functions at a time separated by a comma (so a cell might look like: "coffee, paperwork"). Will this solution still work, or is there a way to make it work so that both coffee and paperwork in that case get +1 ? – SRag Jul 12 '16 at 20:29
Hi, This code wont do that for you in its current state - have a look at this link, it may pass on some ideas - http://stackoverflow.com/questions/33881040/vba-passing-multiple-values-to-instr – dyslexicgruffalo Jul 14 '16 at 08:39

Sgdva · Answer 2 · 2016-07-11T15:43:40.883

I think this kind of does the trick, it should guide you to the right path if needed some extra "thingies". Code in VBA:

Function PersonSchedule(RangeTime As Range, RangeToCountIn As Range, ActivityToAnalyze As String) As Double
'if ranges are not the same that's an error
'Err01PersonSchedule: Range for time should be the same size as elements within it!"
If RangeTime.Columns.Count <> RangeToCountIn.Columns.Count Or RangeTime.Rows.Count > 1 Or RangeToCountIn.Rows.Count > 1 Then PersonSchedule = CVErr(xlErrRef): Exit Function
Dim TimeDifference As Double
Dim CounterColumnsInRange As Long
Dim TotalColumnsInRange As Long: TotalColumnsInRange = RangeToCountIn.Column + RangeToCountIn.Columns.Count - 1
For CounterColumnsInRange = RangeToCountIn.Column To TotalColumnsInRange
'the "If" statement is to handle the "0.5" when to multiply the time specified or not
'I can see this is when there is no "30" minutes in the middle, I don't fully understand
'the logic behind it, but, if it works for you, it's ok
'Ideally, time stamps are per column in the RangeTime
If CounterColumnsInRange < TotalColumnsInRange - 1 Then ' 1. If CounterColumnsInRange < TotalColumnsInRange
On Error GoTo xErr02PersonSchedule
'make sure time is time (I know it's silly, but this is if the headers are      not "time")
TimeDifference = DateDiff("n", Cells(RangeTime.Row, CounterColumnsInRange).Value, Cells(RangeTime.Row, CounterColumnsInRange + 1).Value) / 60
Else ' 1. If CounterColumnsInRange < TotalColumnsInRange
TimeDifference = 1
End If ' 1. If CounterColumnsInRange < TotalColumnsInRange
'seems like if there's one hour difference just sum up "normal"
If TimeDifference <> 1 And Cells(RangeToCountIn.Row, CounterColumnsInRange).Value = ActivityToAnalyze Then ' 2. If TimeDifference <> 1 And Cells(RangeToCountIn.Row, CounterColumnsInRange).Value = ActivityToAnalyze
PersonSchedule = TimeDifference + PersonSchedule
ElseIf Cells(RangeToCountIn.Row, CounterColumnsInRange).Value = ActivityToAnalyze Then: PersonSchedule = 1 + PersonSchedule ' 2. If TimeDifference <> 1 And Cells(RangeToCountIn.Row, CounterColumnsInRange).Value = ActivityToAnalyze
End If ' 2. If TimeDifference <> 1 And Cells(RangeToCountIn.Row, CounterColumnsInRange).Value = ActivityToAnalyze
Next CounterColumnsInRange
If 1 = 2 Then '99. If error
xErr02PersonSchedule:
PersonSchedule = CVErr(xlErrValue)
End If '99. If error
End Function

Excel: Dynamic counter for qualitative data

2 Answers2