Counting unique values across multiple cells...but utilizing color

[Darren_workforce]

Hello,

I have a spreadsheet listing queue staff from B through J. The queue name is in cell 1 and the agent names within each queue take up the rest of the columns. My formula below works to count up all unique individuals across the spreadsheet but it obviously does not take into account any color coding I've assigned to each agent (full-time, part-time, etc). I have been unable to locate anything online so I was curious if the formula below could be adjusted with vba to count up not only the unique names but also only those names that have a particular color code? (If it helps, my full-timers are coded with conifer green 146, 208, 80 || #92D050)

=SUMPRODUCT((B2:J35<>"")/(COUNTIF(B2:J35,B2:J35)+(B2:J35="")))

Thank you in advance for any assistance!!

 

[Eric W]

Try this: Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Public Function UniqueColorCount(r As Range, cr As Range)
Dim d As Object, cel As Variant

    Set d = CreateObject("Scripting.Dictionary")
    
    For Each cel In r
        If cel.Interior.Color = cr.Interior.Color And cel.Value <> "" Then d(cel.Value) = d(cel.Value) + 1
    Next cel
    
    UniqueColorCount = d.Count

End Function

Go back to Excel and enter the formula as follows:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Current	B	C	D	E	F	G	H	I	J
2	5		Ed
3
4	VBA function			Ed					Jane
5	2
6						Al
7										Jen
8				Mary
9
10
11								Mary
12
13		Jen						Mary
14
Sheet3

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT((B2:J35<>"")/(COUNTIF(B2:J35,B2:J35)+(B2:J35="")))
A5	A5	=uniquecolorcount(B2:J35,A10)

The first parameter is the range, the second parameter is a cell with the color you want to count. I thought it would be easier than trying to figure out the color code of every color you might want to use. Also note that this will only count manually colored cells. Conditional Formatted cells won't be counted, although that can be done.

Let us know how this works.

 

[Darren_workforce]

Try this: Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Public Function UniqueColorCount(r As Range, cr As Range)
Dim d As Object, cel As Variant

    Set d = CreateObject("Scripting.Dictionary")
   
    For Each cel In r
        If cel.Interior.Color = cr.Interior.Color And cel.Value <> "" Then d(cel.Value) = d(cel.Value) + 1
    Next cel
   
    UniqueColorCount = d.Count

End Function

Go back to Excel and enter the formula as follows:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Current	B	C	D	E	F	G	H	I	J
2	5		Ed
3
4	VBA function			Ed					Jane
5	2
6						Al
7										Jen
8				Mary
9
10
11								Mary
12
13		Jen						Mary
14
Sheet3

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT((B2:J35<>"")/(COUNTIF(B2:J35,B2:J35)+(B2:J35="")))
A5	A5	=uniquecolorcount(B2:J35,A10)

The first parameter is the range, the second parameter is a cell with the color you want to count. I thought it would be easier than trying to figure out the color code of every color you might want to use. Also note that this will only count manually colored cells. Conditional Formatted cells won't be counted, although that can be done.

Let us know how this works.

Sorry Eric. I should've mentioned there is conditional formatting for the colors, I apologize. I have another sheet that tracks each agent's employment status and then shades the cells in Sheet1 accordingly. I manually shaded all names green and both of your formulas count 57 unique names so they work correctly. However, since the colors are determined by CF from the other sheet, how would that be taken into account?

 

[Eric W]

Change the function to:

Public Function UniqueColorCount(R As Range, cr As Range)
Dim d As Object, cel As Variant, CFColor As Long

    Application.Volatile
    Set d = CreateObject("Scripting.Dictionary")
    
    For Each cel In R
        CFColor = Evaluate("Helper(" & cel.Address() & ")")
        If CFColor = cr.Interior.Color And cel.Value <> "" Then d(cel.Value) = d(cel.Value) + 1
    Next cel
    
    UniqueColorCount = d.Count

End Function


Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

This required a bit of a trick, since the DisplayFormat property is not usually available in a function. Also note that changing a cell's color does not automatically trigger a recalculation, so you might have to press F9 to get it to update. But if your CF rules are based on some kind of a formula, you might be OK.

 

[Darren_workforce]

Change the function to:

Public Function UniqueColorCount(R As Range, cr As Range)
Dim d As Object, cel As Variant, CFColor As Long

    Application.Volatile
    Set d = CreateObject("Scripting.Dictionary")
   
    For Each cel In R
        CFColor = Evaluate("Helper(" & cel.Address() & ")")
        If CFColor = cr.Interior.Color And cel.Value <> "" Then d(cel.Value) = d(cel.Value) + 1
    Next cel
   
    UniqueColorCount = d.Count

End Function


Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

This required a bit of a trick, since the DisplayFormat property is not usually available in a function. Also note that changing a cell's color does not automatically trigger a recalculation, so you might have to press F9 to get it to update. But if your CF rules are based on some kind of a formula, you might be OK.

OK that's perfect! Does exactly what I was hoping it would do. As I changed cell colors, the total did change. Thank you very much for the help on this one, Eric!

 

[Eric W]

Happy to help!