Is There a Way to count Cell Background Color under Each Column Without Filtering

[andyka]

Hi Experts
Is there a way to count How many Cells are Green, Amber and Blue above each Column without filtering the Data.
Please ignore Dates as some will not have dates. I just want to count the colors above each column.
Having VBA is ok However, formula would be better if possible

Gantt with dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	KEY	Not Started			1	2	1
2		InProgress			2	5	5	4	4	4	4	3	2	1	1	1
3		Completed			2	2	2
4	Activity Name	Start	Finish	Activity Status	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22
5	29-Oct-21
6	10670.5.11 OST - P1
7	SG 0 - TRA (10%)	02/10/21	29/11/21	Not Started	02/10/21	29/11/21
8	Stage Gate 0 Complete		29/11/21	InProgress		29/11/21
9	Start Definition Phase	12/10/21		Completed	12/10/21
10	10670.5.12 OST - P2
11	SG 0 - TRA (10%)	14/10/21	29/12/21	InProgress	14/10/21		29/12/21
12	Stage Gate 0 Complete		29/11/21	Not Started		29/11/21
13	Stage Gate 1 Approved		29/12/21	Not Started			29/12/21
14	Summary - Concept (Planning Package)	22/12/21	20/02/22	Not Started			22/12/21		20/02/22
15	SG0 to SG1 LOE	18/11/21	15/04/22	Not Started		18/11/21					15/04/22
16	10670.5.15 OST - 07
17	Receive Expression of Interest from AWE Sponsor	29/10/21		Completed	29/10/21
18	Start	18/12/21		Completed			18/12/21
19	02-Nov-21
20	10670.5.12 OST - P2
21	Update Risk Register	14/11/21	02/09/22	Not Started		14/11/21										02/09/22
22	Update MDAL	28/03/22	02/05/22	Not Started						28/03/22		02/05/22
23	04-Nov-21
24	10670.5.12 OST - P2
25	Risk Register Review	23/10/21	04/06/22	Not Started	23/10/21								04/06/22
26
27
Sheet1 (2)

Cell Formulas
Range	Formula
E5:R25	E5	=IFERROR(IFS(DATE(YEAR($B5),MONTH($B5),1)=E$4,$B5,DATE(YEAR($C5),MONTH($C5),1)=E$4,$C5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:R25	Expression	=AND($D5=$B$2,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$3,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$1,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO

 

[offthelip]

try this user defined function function which you can put in E1 in your workbook and then use:

=countbycolor(E1,E5:E50)

'modify to suit your range

Function countByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = cSum + 1
End If
Next cl
countByColor = cSum
End Function

Note I modifed this from a function I used to sumbycolor
It counts the number of cells in the second range that match the color in the first range

 

[andyka]

What I'm asking is if col A defines the last row with data (the last row of concern), and if there's any cell coloured anywhere below that row, it doesn't matter.

Hi Micron
Yes col A does defines the last row with data

 

[andyka]

try this user defined function function which you can put in E1 in your workbook and then use:

=countbycolor(E1,E5:E50)

'modify to suit your range

Function countByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = cSum + 1
End If
Next cl
countByColor = cSum
End Function

Note I modifed this from a function I used to sumbycolor
It counts the number of cells in the second range that match the color in the first range

Hi Micron
I am getting error #NAME?
will it work for all other two colors in E2 and E3 also?
I have Blue in E1: I wants to know how many blue in E5:E50
I have Amber in E2: I wants to know how many blue in E5:E50
I have Greenin E3: I wants to know how many blue in E5:E50

 

[offthelip]

Where have you put the code?? Because the Name error implies that excel can't find the function, It needs to be in a standard module in the project of the workbook you are using it in
It does work for any color, it detects the color to look for by selecting a single cell as the first "range" in my example, this can be the same cell that you put the function into,. so if you :

=countbycolor(E1,E5:E50)

into Cells E1 it will count yellows

=countbycolor(E2,E5:E50)

into E2 it will count blues, and thus:

=countbycolor(E3,E5:E50)

into E3 will count the greens , you can then copy the three rows acorss the other columns
You could point all the functions at cells C1 , C2 and C3 which will then work regardless of whether the top three rows are colored
Note I am not Micron!!

 

[Micron]

@andyka
This is the result I get with what I have. If it's right then I'll post the code. The counting starts in E5 - could start in E7. What matters is how the code is called and I don't know how you want to do that. Could be: sheet activate or anything else that's suitable BUT I think some of the available evenst depends on knowing the last column with data and I suspect that is not known. So if a function call and your columns begin to exceed the last column with the function, you'd have to drag it, same as you would a formula.

 

[andyka]

Where have you put the code?? Because the Name error implies that excel can't find the function, It needs to be in a standard module in the project of the workbook you are using it in
It does work for any color, it detects the color to look for by selecting a single cell as the first "range" in my example, this can be the same cell that you put the function into,. so if you :

=countbycolor(E1,E5:E50)

into Cells E1 it will count yellows

=countbycolor(E2,E5:E50)

into E2 it will count blues, and thus:

=countbycolor(E3,E5:E50)

into E3 will count the greens , you can then copy the three rows acorss the other columns
You could point all the functions at cells C1 , C2 and C3 which will then work regardless of whether the top three rows are colored
Note I am not Micron!!

Mi Micron
Thank you for your continues help. I am sorry I am new to VBA.
I have included the worksheet where I have added all the codes but I am getting Zero for all of them

Gantt with dates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	KEY	Not Started		Desire Results-->	0	0	0	0	0	0	0	0	0	0	0	0	0
2		InProgress		Desire Results-->	0	0	0	0	0	0	0	0	0	0	0	0	0
3		Completed		Desire Results-->	0	0	0	0	0	0	0	0	0	0	0	0	0
4	Activity Name	Start	Finish	Activity Status	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22
5	29-Oct-21
6	10670.5.11 OST - P1
7	SG 0 - TRA (10%)	02/10/21	29/11/21	Not Started
8	Stage Gate 0 Complete		29/11/21	InProgress
9	Start Definition Phase	12/10/21		Completed
10	10670.5.12 OST - P2
11	SG 0 - TRA (10%)	14/10/21	29/12/21	InProgress
12	Stage Gate 0 Complete		29/11/21	Not Started
13	Stage Gate 1 Approved		29/12/21	Not Started
14	Summary - Concept (Planning Package)	22/12/21	20/02/22	Not Started
15	SG0 to SG1 LOE	18/11/21	15/04/22	Not Started
16	10670.5.15 OST - 07
17	Receive Expression of Interest from AWE Sponsor	29/10/21		Completed
18	Start	18/12/21		Completed
19	02-Nov-21
20	10670.5.12 OST - P2
21	Update Risk Register	14/11/21	02/09/22	Not Started
22	Update MDAL	28/03/22	02/05/22	Not Started
23	04-Nov-21
24	10670.5.12 OST - P2
25	Risk Register Review	23/10/21	04/06/22	Not Started
26
Sheet1 (2)

Cell Formulas
Range	Formula
E1:Q3	E1	=countbycolor($C1,E$5:E$50)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:R25	Expression	=AND($D5=$B$2,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$3,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$1,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO

 

[Micron]

Post script to my prior post.
Please stop mixing up your responders.
I think offthelip's code is more concise since it doesn't require finding the last used column (however, it seems to be limited to 50 rows). If you can use that code or a version of it, that might be better.

EDIT forgot to mention before that there's no data in A26 so even though there's a blue cell in that row, it's not counted.

 

[Micron]

this is not what you were given

=countbycolor($C1,E$5:E$50)

 

[andyka]

this is not what you were given

=countbycolor($C1,E$5:E$50)

Hi Micron
I tried your version to but same answer. "ZERO"

 

[Micron]

=countbycolor($C1,E$5:E$50)

I have given you no version of anything. If you can't straighten out who is replying here then unfortunately I will have to bow out.
In that post, I merely pointed out what was different without giving you what I think is the right function call since it's not my code and I don't want to cut in on someone else's advice here. Besides, you were given the correct call in post 14.