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

Darren_workforce

Board Regular
Joined
Oct 13, 2022
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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)

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

Thank you in advance for any assistance!!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:

VBA Code:
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
ABCDEFGHIJ
1CurrentBCDEFGHIJ
25Ed
3
4VBA functionEdJane
52
6Al
7Jen
8Mary
9
10
11Mary
12
13JenMary
14
Sheet3
Cell Formulas
RangeFormula
A2A2=SUMPRODUCT((B2:J35<>"")/(COUNTIF(B2:J35,B2:J35)+(B2:J35="")))
A5A5=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.
 
Upvote 0
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:

VBA Code:
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
ABCDEFGHIJ
1CurrentBCDEFGHIJ
25Ed
3
4VBA functionEdJane
52
6Al
7Jen
8Mary
9
10
11Mary
12
13JenMary
14
Sheet3
Cell Formulas
RangeFormula
A2A2=SUMPRODUCT((B2:J35<>"")/(COUNTIF(B2:J35,B2:J35)+(B2:J35="")))
A5A5=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?
 
Upvote 0
Change the function to:

VBA Code:
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.
 
Upvote 0
Solution
Change the function to:

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top