Counting multiple continuous ranges and totalling

[welshcuriosity]

Hello,

I've been asked by my manager in work to see if I can adapt a spreadsheet to work out people's Bradford Factor (a way of monitoring people's sickness https://en.wikipedia.org/wiki/Bradford_Factor)

At the moment, they are using a spreadsheet with people's names in column A (starting at A2), and then on each person's row they use different coloured cells to monitor different absent reasons (one colour for holidays, one for sickness etc). A1 onwards lists the dates.

A very crude example is below - (red) is a red coloured cell, there is no text in the cell:

01/01/15 AM

01/01/15 PM

02/01/15 AM

02/01/15 PM

03/01/15 AM

03/01/15 PM

04/01/15 AM

04/01/15 PM

...

...

29/03/15 AM

29/03/15 PM

30/03/15 AM

30/03/15 PM

Alice

(red)

(red)

(red)

(red)

(red)

(red)

Bob

(red)

(red)

(red)

(red)

<tbody>
</tbody>

I need to work out 2 things from the data:

How many total days have been coloured red,
How many instances of red days (number of continuous red squares)

Using the table above, it would work out as:
Alice:
Total days = 3 (1.5 days worth in the first continuous red section, half a day on 04/01/15 AM, and 1 whole day from 29/03/15 PM to 30/03/15 AM. A dirty way to work out would be the total number of red cells / 2)
Instances = 3 (1 instance from 01/01/15 AM to 02/01/15 AM, 1 instance on 04/01/15 AM, and 1 instance from 29/03/15 PM to 30/03/15 AM)
Bradford Factor = 27 (3 instances squared x 3 days = 9 x 3)
Bob:
Total days = 2
Instances = 2
Bradford Factor = 8 (2 instances squared x 2 days = 4 x 2)

I've been able to work out how to count the total number of coloured squares using a function from https://support.microsoft.com/en-gb/kb/2815384 so I can work out half of the Bradford Factor data, but that's where I'm getting stuck.

Does anyone know how to count the number of instances in a given range?

 

[mumps]

Try this macro.

Sub test()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    Dim counter As Long
    counter = 0
    For x = 2 To LastRow
        For Each rng In Range(Cells(x, 2), Cells(x, lCol))
            If rng <> "" And rng.Offset(0, 1) = "" Then
                counter = counter + 1
            End If
        Next rng
        MsgBox counter & " instances for " & Cells(x, 1)
        counter = 0
    Next x
    Application.ScreenUpdating = True
End Sub

The variable 'counter' represents the number of instances.

 

[welshcuriosity]

That works perfectly, thanks

How much work would it be for me to turn it into a function that works on a single row so that I can use the result in a worksheet?

Something like =NumInstances(B2:Z2)

 

[mumps]

Click on any cell in the row you want to work with and then run this macro:

Sub test()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    Dim counter As Long
    counter = 0
    For Each rng In Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, lCol))
        If rng <> "" And rng.Offset(0, 1) = "" Then
            counter = counter + 1
        End If
    Next rng
    MsgBox counter & " instances for " & Cells(ActiveCell.Row, 1)
    counter = 0
    Application.ScreenUpdating = True
End Sub