Counting Cells by Color with conditional formatting

[romancristiani]

Each row represents a location. Each column is a metric that we track for each location. The metrics and goals are the same for each location. Each cell changes color based on conditional formatting. The cells of each column have the same conditions but the conditions change per column. The cells change constantly. New tabs are created monthly for each district. I need the total of the like colored cells to compute to the matching rows of the color coded columns below. Is this possible?

@Hans Vogelaar

[#2]

@romancristiani 

Maybe this link will help you :).

sum by color when colors are set by conditional formatting

[#3]

@romancristiani 

Do you really want to sum the values of (for example) red cells, or do you want to count them?

[#4]

Count them! Sorry

[#5]

I need to total number of Reds, Yellows and Greens in the respective color column below per location. Also Rows 4-7 that don't show in the photo above were deleted.

[#6]

[#7 · Accepted solution]

@romancristiani 

Here is the VBA code I came up with:

Function CountColor(rng As Range, colorcell As Range) As Long
    Dim cell As Range
    Dim clr As Long
    clr = colorcell.Interior.Color
    For Each cell In rng
        If Evaluate("GetColor(" & cell.Address(External:=True) & ")") = clr Then
            CountColor = CountColor + 1
        End If
    Next cell
End Function

Function GetColor(cell As Range) As Long
    GetColor = cell.DisplayFormat.Interior.Color
End Function

The formula in B20 is

=IF($A20="", "", CountColor($B8:$P8, B$19))

This can be filled to the right to D20, ten down to row 25.

I have attached the workbook to a PM to you. You'll have to allow macros when you open it.

[#8]

Hans,

This is incredible. Everything works perfectly. I truly appreciate your help! Let me know if there is anything you need.

[#9]

@romancristiani 

Good to hear that it works.