Cell count by multiple conditions

[sharpeye]

Hello everyone
I need a little help. Im tring to do a count of cells based on colours. The first colour is the font which, if the font is yellow (based on the probability of a result being over a certain value, in this case 85%+.) when the result is positive, I colour the cell green, if not I colour the cell red. Id like to count all the cells in a given range that are both green in colour with yellow font. I already have the commands in place that can count cell colour and font colour but have no idea how to combine both in a count.

I count the font colour using
=CountCellsByFontColor(M2:M95,M1)

I count cell colour using
=CountCellsByColor(M2:M95,M1)

I would like to create a cell that would only count the cells that meet both criteria
Any ideas would be amazing
Many thanks

 

[kevin9999]

You seem to have a couple of UDFs there. Here's an alternative that will count where both cell color and font color match a given cell.

Public Function CountAllColors(RangeToCheck As Range, CellFormat As Range) As Long
    Application.Volatile
    Dim c As Range, x As Long
    x = 0
    For Each c In RangeToCheck
        If c.Font.Color = CellFormat.Font.Color And c.Interior.Color = CellFormat.Interior.Color Then x = x + 1
    Next c
    CountAllColors = x
End Function

 

[Alex Blakenburg]

Why don't you just Countifs using the original logic ?

 

[sharpeye]

Hi guys, thanks for the ideas.
I havent had a chance yet to try out your suggestions but I just thought Id throw this in there as it might make for an easier solution.
The cells that I would like to count with the yellow text only has yellow text if the value of that text is 80% or above so it could be that instead of counting its text colour, its value could be counted instead.

Just to confirm, there are 4 different colour combinations in the cell range to be counted,
Green cell below 80% or Black text
Red cell below 80%, Black text
Green cell above 80% or Yellow text (These are the cells Id like to count)
Red cell above 80%, Yellow text

Hope this helps and apologies for not including this in my original post

 

[Alex Blakenburg]

What column has the percentages that you are referring to ?
What is the formula / criteria for the interior color ?
What is the formula / criteria for the font color ?
Without referring to color what is it you are wanting to count ?

 

[sharpeye]

This is a sample of the values Im using. The Yellow font is generated using a conditional format where the value >= 80%
The Red and Green cell colours are manually filled in by me depending on a true or false result.
What I need is a formula that will count the green cells that contain yellow text or green cells with a value of >=80%
In this sample there are 13 cells id like to count out of the 25 cells in the sample

 

[rlv01]

I think you may need to explain the conditional formatting rules you are using. Example:

Book2
	A	B	C	D
1	81%
2	91%
3	11%
4	86%
5	96%
6	11%
7	81%
8	91%
9	11%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Cell Value	>0.95	text	NO
A1:A9	Cell Value	>0.85	text	NO
A1:A9	Cell Value	>0.9	text	NO
A1:A9	Cell Value	>0.8	text	NO

Using this free tool can make that easier

 

[sharpeye]

I think you may need to explain the conditional formatting rules you are using. Example:

Book2
	A	B	C	D
1	81%
2	91%
3	11%
4	86%
5	96%
6	11%
7	81%
8	91%
9	11%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Cell Value	>0.95	text	NO
A1:A9	Cell Value	>0.85	text	NO
A1:A9	Cell Value	>0.9	text	NO
A1:A9	Cell Value	>0.8	text	NO

Using this free tool can make that easier

I basically use a conditional formatting rule to colour any value 80% or over in yellow text and then I remove the conditional formatting rules and the text stays yellow.
I then change the cell colour to either red or green manually so by the time I get to the stage where i need to count the green cells with yellow text, there is no conditional formtting in play

 

[rlv01]

Green cell above 80% or Yellow text (These are the cells Id like to count)

Book2
	A	B	C
1	85%	1
2	91%	0
3	11%	0
4	86%	0
5	96%	1
6	11%	0
7	55%	0
8	91%	0
9	82%	1
10	91%	0
11		3	<- Number of cells where color is green and value >= 80%
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(AND(CellColor=4,A1>=0.8),1,0)
B11	B11	=SUM(B1:B10)

Where the formula CellColor is defined as a formula using the name manager and the Get.Cell function

Name: "CellColor". RefersTo "=GET.CELL(63,INDIRECT("rc[-1]",FALSE))"

 

[kevin9999]

The way I interpreted your original post was that you were using UDFs to count the number of cells in a given range - M2:M95 - where the cells' format matched the format of another given cell - M1. The UDF I wrote in post #2, when placed in a standard module and applied to the same ranges using some sample data, works like this:

test.xlsb
	M	N	O	P	Q	R	S
1	1%	= 'template' cell
2	81%	46	= Count of cells in M2:M95 matching 'template'
3	91%
4	73%
5	85%
6	75%
7	76%
8	95%
9	87%
10	74%
11	85%
12	84%
13	73%
14	81%
15	77%
16	72%
17	93%
18	87%
19	94%
20	93%
21	89%
22	98%
23	75%
24	88%
25	92%
26	70%
27	81%
28	81%
29	91%
30	73%
31	85%
32	75%
33	76%
34	95%
35	87%
36	74%
37	85%
38	84%
39	73%
40	81%
41	77%
42	72%
43	93%
44	87%
45	94%
46	93%
47	89%
48	98%
49	75%
50	88%
51	92%
52	70%
53	81%
54	81%
55	91%
56	73%
57	85%
58	75%
59	76%
60	95%
61	87%
62	74%
63	85%
64	84%
65	73%
66	81%
67	77%
68	72%
69	93%
70	87%
71	94%
72	93%
73	89%
74	98%
75	75%
76	88%
77	92%
78	70%
79	81%
80	81%
81	91%
82	73%
83	85%
84	75%
85	76%
86	95%
87	87%
88	74%
89	85%
90	84%
91	73%
92	81%
93	77%
94	72%
95	93%
96
Sheet3

Cell Formulas
Range	Formula
N2	N2	=CountAllColors(M2:M95,M1)

I'd be keen to know if you were able to try it out, or if I'd misinterpreted your original intention.