determine percentage of values matching another column

[Redbull753]

A bit of a complicated question. I want a column to calculate the percentage of its cells matching another column. If a solution can be found without VBA, that would be ideal.

Conditional formatting already looks to see if columns C, D, E, F, G, AND H match the value of column B and highlight it green if it goes. The % match at the bottom of the column shows how much of the column matches the control column B and is currently entered manually but I would like to see if there is a way to calculate it with a function. Thanks.

 

[lrobbo314]

Book1
	B	C	D	E	F	G	H
1	B	D	B	A	A	D	D
2	B	C	A	C	D	A	B
3	B	D	D	D	D	D	D
4	A	B	B	C	C	A	B
5	C	D	B	B	A	A	C
6	C	B	D	A	D	A	A
7	D	A	B	A	B	A	C
8	C	B	D	C	A	D	C
9	A	D	C	B	D	C	A
10	A	C	C	A	B	D	D
11	C	A	B	A	B	C	A
12	C	C	C	C	C	A	C
13	B	B	C	A	D	C	C
14	B	C	C	C	A	D	C
15	C	A	C	A	B	A	A
16	D	A	A	D	C	B	D
17	C	D	D	C	A	A	C
18	A	C	D	C	D	C	D
19	D	D	D	C	D	A	B
20	D	A	C	A	A	A	A
21	D	A	D	D	B	A	D
22	D	B	A	B	B	B	D
23	B	D	C	D	C	D	A
24	C	C	A	C	A	D	C
25	D	D	D	C	D	A	B
26	D	C	B	A	D	D	B
27	C	B	B	C	B	C	B
28	A	B	C	C	D	B	C
29	C	A	B	A	C	D	B
30	D	C	A	D	B	A	C
31	B	C	A	B	B	C	A
32	B	A	A	A	A	D	B
33	A	C	D	D	D	A	C
34	C	B	C	C	D	C	B
35	A	D	C	D	C	B	B
36	A	C	C	A	C	D	B
37	C	B	B	C	D	C	C
38	C	B	C	C	B	B	B
39		13.16%	34.21%	26.32%	23.68%	18.42%	26.32%
Sheet1

Cell Formulas
Range	Formula
B1:H38	C1	=CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")
C39:H39	C39	=SUMPRODUCT(--(C1:C38=B1:B38))/COUNTA(C1:C38)

 

[Fluff]

Another option

+Fluff v2.xlsm
	B	C	D	E	F	G	H
1	C	C	D	B	A	A	C
2	B	A	D	C	A	B	C
3	A	D	D	B	D	A	B
4	A	C	C	B	A	A	A
5	B	A	A	D	D	D	C
6	A	B	B	D	B	A	B
7	B	D	A	D	A	A	A
8	C	C	B	A	D	A	B
9	C	C	B	B	A	C	B
10	D	A	C	C	A	C	D
11	C	C	B	A	D	D	B
12	D	C	A	D	D	C	B
13	B	B	B	C	D	A	A
14	C	B	D	C	D	D	B
15	A	B	A	D	C	D	C
16	B	A	A	B	D	A	B
17	C	D	C	A	B	C	C
18	A	D	C	A	C	C	C
19	A	A	C	D	B	A	A
20	C	A	B	A	A	D	C
21	B	C	B	B	D	B	B
22	D	B	C	B	D	C	B
23	B	A	A	A	D	D	D
24	D	D	A	A	A	B	A
25	A	B	D	C	C	C	A
26	D	B	A	B	C	B	D
27	D	D	B	A	B	A	B
28	B	C	A	A	D	A	C
29	B	D	B	C	A	A	A
30	A	D	A	D	B	A	C
31	C	A	D	A	A	B	B
32	D	C	A	C	A	C	B
33	A	A	A	C	D	A	D
34	A	D	C	C	A	D	B
35	D	D	D	B	A	A	C
36	D	A	D	D	D	C	B
37	B	D	B	A	B	B	D
38	B	C	B	A	D	A	C
39		26%	29%	16%	16%	29%	26%
Main

Cell Formulas
Range	Formula
C39:H39	C39	=COUNT(FILTER(ROW(C1:C38),C1:C38=$B$1:$B$38))/COUNTA(C1:C38)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:H38	Expression	=C1=$B1	text	NO

 

[Redbull753]

Thank you very much! that works perfectly!!!

 

[Redbull753]

This forum is the best. A real life saver. Many thanks to everyone who helps out!

 

[Fluff]

Glad we could help & thanks for the feedback.