Count cells based on colour & value(text)

[Paul Naylor]

Hi running 97 at work. Got vba code to count cells based on colour . Now want to be able to count based on contents ( text - names) and their colour. How do.i do it in both 97 and 2018?

 

[Yongle]

Is this what you want

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​
1​	Description	Type		formula in D2
2​	Cat	Feline	3​	=CountText(C:C,C2)
3​	Dog	Canine
4​	Mouse	Rodent
5​	Lion	Feline
6​	Tiger	Feline
7​

Sheet: Other Sheet

Goes in STANDARD module

Function CountText(rng As Range, countWhat As String)
    Application.Volatile
    CountText = WorksheetFunction.CountIf(rng, countWhat)
End Function

 

[Paul Naylor]

Is this what you want

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​
1​	Description	Type		formula in D2
2​	Cat	Feline	3​	=CountText(C:C,C2)
3​	Dog	Canine
4​	Mouse	Rodent
5​	Lion	Feline
6​	Tiger	Feline
7​

Sheet: Other Sheet

Goes in STANDARD module

Function CountText(rng As Range, countWhat As String)
    Application.Volatile
    CountText = WorksheetFunction.CountIf(rng, countWhat)
End Function

Thanks for taking time . Not quite what wanted my fault. Column A is list of account numbers which coloured depending upon whether checked ( green checked , yellow unchecked).Column C is list of names of who's checking them. What I want is to count how many a certain colleague has left to check.

Tried your code , presume just need key code at bottom into vba? and for me it's just counting names not cell colour. Sorry include on postage stamp what know about vba.

Heres code using to count cells based on colour:

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

Regards,
Paul

 

[Yongle]

Is this what you want ?

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Value	??	User			RESULTS			Formula in G2 copied down and across
2​	Acc_001		Jenny			Jenny	2​	7​	=CountColorUser($A$2:$A$24,G$1,$C$2:$C$24,$F2)
3​	Acc_002		Jenny			Jacob	7​	0​
4​	Acc_003		Jenny			Joe	4​	3​
5​	Acc_004		Jacob
6​	Acc_005		Joe
7​	Acc_006		Jenny
8​	Acc_007		Jacob
9​	Acc_008		Jenny
10​	Acc_009		Joe
11​	Acc_010		Joe
12​	Acc_011		Jenny
13​	Acc_012		Jacob
14​	Acc_013		Joe
15​	Acc_014		Jacob
16​	Acc_015		Jacob
17​	Acc_016		Joe
18​	Acc_017		Jacob
19​	Acc_018		Jenny
20​	Acc_019		Jenny
21​	Acc_020		Joe
22​	Acc_021		Jenny
23​	Acc_022		Joe
24​	Acc_023		Jacob

Sheet: Sheet3

Function CountColorUser(range_data As Range, criteria As Range, range_user As Range, user As Range) As Long
    Dim dataX As Range, Xcolor As Long, c As Long
    Application.Volatile
    Xcolor = criteria.Interior.ColorIndex
    c = range_user.Column - range_data.Column
    For Each dataX In range_data
        If dataX.Interior.ColorIndex = Xcolor And dataX.Offset(, c) = user Then CountColorUser = CountColorUser + 1
    Next dataX
End Function