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

 

[Micron]

above each Column

what does that mean, put 3 count values in row one from E to P (or whatever)? I think you should show the desired result.

 

[andyka]

what does that mean, put 3 count values in row one from E to P (or whatever)? I think you should show the desired result.

Hi Micron
The desired results are already shown in cell E1 to P3
I just want the colors to be counted above each column as shown between E1 to P3.
for example: In column E I have data between E5 to E25 and there are 1 orange, 2 green 2 blue. I color coded them wrong in sheet. But I want E1 to P3 to be automatically color coded for the color shown in column for example there is only Blue color in Column H5 to H25 so the resultant value only should show 4 in H1
I hope is was helpful.
Thank you in advance

 

[andyka]

Updates sheet

Gantt with dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	KEY	Not Started		Desire Results-->	2	5	5	4	4	4	4	3	2	1	1	1
2		InProgress		Desire Results-->	1	2	1
3		Completed		Desire Results-->	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
28
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

 

[Micron]

I didn't see the scroll bar and thought you posted a section several rows down. My bad.
If there is a formula for this I'll never be able to provide it as they're not my thing. Should be doable with code though. Want to wait for a formula?

 

[andyka]

I didn't see the scroll bar and thought you posted a section several rows down. My bad.
If there is a formula for this I'll never be able to provide it as they're not my thing. Should be doable with code though. Want to wait for a formula?

Hi Micron

Although, I would love to know formula as its a challenging post but please provide VBA if possible.

 

[Micron]

I'll see what I can do. I'm much better at Access vba so this will take a bit of time.

 

[Micron]

After an hour or so I've decided there is no such thing as UsedRange for a column unless one creates a function for that. So a key element, as far as I'm concerned: if any cell is coloured below the last row that contains data in col A, it is of no consequence? In other words, A25 is the last A row with data. If someone coloured E30, it should be ignored because it comes after A25.

 

[andyka]

Hi Micron

I actually have data in column E5:AR675

So, is the solution possible?

I just wants to know how many blue,green,Amber cells are under each column without filtering data but although I will be using filter to filter data for other purposes such as project id, dates etc..

 

[Micron]

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.