Counting cells based on their colour and other criteria

Question

I'm working on some rostering functions in Excel, with the basis being that we have a limited number of people that will need to be rostered-on for work, covering shifts at multiple sites

Roster Conflict - Joe Bloggs 4 @ two sites on 21/5/2014

When planning the rosters, we use a simple set of coloured cells to fill-in for when people are on dayshift, nightshift, not-rostered, annual leave, etc. Given that there are multiple sites we plan for I need to have a quick formula that checks whether that individual has been rostered-on for simultaneous shifts at multiple sites (obviously not possible), so we can easily identify when there is a conflict in the planning stage. The formula needs to return a TRUE, or value (eg. count >1 means more than one assignment for that person) so that I can use either conditional formatting or VBA to highlight a cell/the row and draw attention to the conflict

What I've tried doing is summing/counting cells that are coloured with a few VBA methods:

Function ISFILLED(MyCell As Range)
    If MyCell.Interior.colorIndex > 0 Then
        Result = True
    Else
        Result = False
    End If
    ISFILLED = Result
End Function

and/or:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If
    ColorFunction = vResult
End Function

These both work fine on their own but I'm unable to combine this with a SUMIFS/COUNTIFS style function to only count the number of cells that are coloured when that individual's name appears against that assignment.

If you have a look in the sample image from the roster, you can see that Joe Bloggs 4 has been assigned a shift at both Site 1 and Site 2 on the 21/05/2014. What I'm after is essentially to count number of coloured cells on row, if the individuals name matching the criteria is against those cells. For this example if would be

=COUNTIFS(C8:AQ8, "Joe Bloggs 4", C12:AQ12, *Cell is Coloured*)

Colour of the cell doesn't matter (hence the first function ISFILLED is better as i don't need a reference cell for the fill), as it's just a sense-check.

Appreciate any help/pointers, as it is, I'm stuck!

You should learn to indent your code properly; this makes it easier for others (and yourself) to read. Did it for you this time. — Jean-François Corbett
thanks - sloppy copy/pasting from chopping bits and pieces together trying to get different outcomes sorry. I'll keep it in mind — Jadyn
I'dd suggest using something with value in those cells and make your life easier (if that's possible). Even a space or a 1 with the same color as background won't show up and you'll suffice with Excel functions — xificurC
@xificurC That will be the solution if i can't get it working through colours - thanks. In practice the roster is chopped/changed a lot during the planning and I was trying to keep it simple so that the flights/accom. people can continue with the scheduling colours they're used to — Jadyn

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2014-05-27T07:43:30

You won't be able to use SUMIFS and COUNTIFS for this. You need something along these lines:

Function IsFilledArr(rng As Range) As Variant
    Dim i As Long, j As Long
    Dim v As Variant
    ReDim v(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            v(i, j) = rng.Cells(i, j).Interior.ColorIndex > 0
        Next j
    Next i
    IsFilledArr = v
End Function

This UDF can be called as an array formula, i.e. select a range of cells, type the formula in the top-left cell (while the whole range is still selected), and then press Ctrl-Shift-Enter. Then you can operate on the returned array, e.g. SUM it.

Example usage:

enter image description here

where the -- double minus converts Boolean TRUE/FALSE values (which are not summable) into 1's and 0's (which are).

I'll let you adapt this to suit your particular requirements.

EDIT: You want more:

calculate the number of filled cells in column C, where there was an adjacent filled cell in column B - in this case C4 would be the only cell that met that criteria.

This formula will do the trick (entered as an array formula):

=SUM(IF(IsFilledArr(B2:B7)+IsFilledArr(C2:C7)>1,1))

Note the use of SUM(IF(...,1)) to mimic COUNT. That is the way to do it when you're dealing with array formulas.

Read more here: http://www.cpearson.com/excel/ArrayFormulas.aspx

You asked for help/pointers; this should hopefully be more than enough to get you unstuck. You can adapt this approach to meet whatever you exact needs are.

Counting cells based on their colour and other criteria

1 Answers