Condition formatting - count how many of the same color OR value appears in the list ?

[MasterBash]

Hello,

I am looking to add more to the conditional formatting from this post here : Using conditional formatting with Table as reference ?

I would repost my excel table using XL2BB, but I forgot it at work. I tried a copy and paste of the table from the link above, but it looked terrible.

Conditional Formatting above is not correct but that is not the point of the my post (In my project, it has an updated formula and it works).

I would like to know how many times each colors (table header of column C, D and E) or each group of values (Values under headers of column C, D and E) appear in column A, input the numbers in column B under each Column 1, 2 and 3.

So if the green color or any of the values under Column D appear 5 times in column A, then I would like of the formula to equal 5 in column B, under Column 2 :

Color or values, but I think values would be a better option than using colors, because if I want to update the colors one day, then the formula may not work anymore. However, I am already using named list for those columns (Lets say, Column1_list, Column2_list, Column3_list), so it may be easier to use the list of values instead of the header colors.

Thank you.

 

[Asbestos_Jen]

Whatever formula you are using to colour the cells can be used to count the values, if I understand your request and its relation to the linked post. Is this what you mean?

Book1
	A	B	C	D	E	F	G	H
1	COUNTS	Column1	Column2	Column3		Column1	Column2	Column3
2	7416165	0	0	0		18871365	7788141654	22226666
3	4984791	0	0	0		8846451	99763146	46411V016A88
4	22226666	0	0	1		29984433	2634169	419647982
5	891919	0	0	0				MR6497226
6	46411V016A88	0	0	1				97141942V47
7	MR6497226	0	0	1				44556987
8	29984433	1	0	0				99988847
9	4547944165	0	0	0
10	18871365	1	0	0
11	2113377916	0	0	0
12	16916149	0	0	0
13	97141942V47	0	0	1
14	469499	0	0	0
15	MR6497226	0	0	1
16	8846451	1	0	0
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=COUNTIF(Table1[[#All],[Column1]],$A2)
C2:C16	C2	=COUNTIF(Table1[[#All],[Column2]],$A2)
D2:D16	D2	=COUNTIF(Table1[[#All],[Column3]],$A2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D16	Expression	=COUNTIF($F$2:$F$8,$A2)>0	text	NO
A2:D16	Expression	=COUNTIF($G$2:$G$8,$A2)>0	text	NO
A2:D16	Expression	=COUNTIF($H$1:$H$8,$A2)>0	text	NO

 

[MasterBash]

Almost.

I would like the total count of column A for all values in each columns of the table (Total count of the values in Column1, Total count of the values in Column2, Total count of Column3).

So if any of the values in column F shows up 5 times in column A, I would just like the total count of it. Column A : 5
In other words, I want to know how many times any of the values in column F shows in Column A, how many times any of the values in column G shows up in the column A and how many times any of the values in column H shows up in column A.

Thank you.

 

[Asbestos_Jen]

I'm working on Excel2013 and the web version of 365. The XL2BB is from 2013, and the jpg is from Web365. You can use the formula below in Excel 365 without array entry.

=SUM(COUNTIF($A$2:$A$16,F2:F9))

2024-10-05.xlsx
	A	B	C	D	E	F	G	H	I
1	COUNTS	Column1	Column2	Column3		Column1	Column2	Column3
2	7416165	0	0	0		18871365	7788141654	22226666
3	4984791	0	0	0		8846451	99763146	46411V016A88
4	22226666	0	0	1		29984433	2634169	419647982
5	891919	0	0	0				MR6497226
6	46411V016A88	0	0	1				97141942V47
7	MR6497226	0	0	1				44556987
8	29984433	1	0	0				99988847
9	4547944165	0	0	0
10	18871365	1	0	0		3	0	5	Total
11	2113377916	0	0	0
12	16916149	0	0	0
13	97141942V47	0	0	1
14	469499	0	0	0
15	MR6497226	0	0	1
16	8846451	1	0	0
17	Total	3	0	5
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=COUNTIF(Table1[[#All],[Column1]],$A2)
C2:C16	C2	=COUNTIF(Table1[[#All],[Column2]],$A2)
D2:D16	D2	=COUNTIF(Table1[[#All],[Column3]],$A2)
B17:D17	B17	=SUM(B2:B16)
F10:H10	F10	=SUM(COUNTIF($A$2:$A$16,F2:F9))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D16,B17:D17	Expression	=COUNTIF($F$2:$F$8,$A2)>0	text	NO
A2:D16,B17:D17	Expression	=COUNTIF($G$2:$G$8,$A2)>0	text	NO
A2:D16,B17:D17	Expression	=COUNTIF($H$1:$H$8,$A2)>0	text	NO

 

[MasterBash]

Thank you ! It works.

 

[Asbestos_Jen]

Glad to have helped; thanks for the feedback.