# color data count

on
In this workbook I'd like

an

count  for every color by column

as shown

data can grown  and shrink as needed

count_by_color_to.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Group Finance Manager
Commented:
I have a UDF that counts,sums or averages by coloured cells.

You can copy the code from this example and use in your own workbooks
ColourFunction.xlsm
ACCOUNTING ASSISTANT

Commented:
an Great Tool

Great  Expert

Thanks !!
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

``````Sub CountByColors()
Dim ws As Worksheet
Dim lr As Long, lc As Long, r As Long, c As Long
Dim clrB As Long, clrP As Long, clrG As Long
Dim cntB As Long, cntP As Long, cntG As Long, cntAM As Long, cntPM As Long
Application.ScreenUpdating = False
Set ws = Sheets("DTA")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr < 8 Then Exit Sub
lc = ws.Range("A8").End(xlToRight).Column
If lc < 3 Then Exit Sub

ws.Range("B2:B3").ClearContents
ws.Range(ws.Cells(4, 3), ws.Cells(6, lc)).ClearContents

clrB = ws.Range("A4").Interior.Color
clrP = ws.Range("A5").Interior.Color
clrG = ws.Range("A6").Interior.Color
For r = 8 To lr
If ws.Range("B" & r).Value = "AM" Then
cntAM = cntAM + 1
ElseIf ws.Range("B" & r).Value = "PM" Then
cntPM = cntPM + 1
End If
Next r
ws.Range("B2").Value = cntPM
ws.Range("B3").Value = cntAM

For c = 3 To lc
For r = 8 To lr
If ws.Cells(r, c).Interior.Color = clrB Then
cntB = cntB + 1
ElseIf ws.Cells(r, c).Interior.Color = clrP Then
cntP = cntP + 1
ElseIf ws.Cells(r, c).Interior.Color = clrG Then
cntG = cntG + 1
End If
Next r
ws.Cells(4, c) = cntB
ws.Cells(5, c) = cntP
ws.Cells(6, c) = cntG
cntB = 0
cntP = 0
cntG = 0
Next c

Application.ScreenUpdating = True
End Sub
``````

In the attached, click the button called "Count By Colors" to run the code.
count_by_color_to.xlsm
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think this is what you asked for. Click the 'Count Hits' button on Sheet1.
29120916.xlsm
ACCOUNTING ASSISTANT

Commented:
in  any ways this   is an  first class work

Thanks

Roy Cox ,  Subodh Tiwari (Neeraj) , Martin Liss
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Group Finance Manager

Commented:
Sorry, I didn't have time last night. I've added the Function for you plus the formulas to count colours in each column.

The advantage of a Function is that it works like an inbuilt Function and will update automatically when a colour changes.
count_by_color_to.xlsm

Do more with