# determine percentage of values matching another column

#### Redbull753

##### New Member
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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### lrobbo314

##### Well-known Member
Cell Formulas
RangeFormula
B1:H38C1=CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")
C39:H39C39=SUMPRODUCT(--(C1:C38=B1:B38))/COUNTA(C1:C38)

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff v2.xlsm
BCDEFGH
1CCDBAAC
4ACCBAAA
6ABBDBAB
9CCBBACB
10DACCACD
13BBBCDAA
14CBDCDDB
16BAABDAB
17CDCABCC
19AACDBAA
21BCBBDBB
22DBCBDCB
24DDAAABA
25ABDCCCA
26DBABCBD
27DDBABAB
29BDBCAAA
32DCACACB
35DDDBAAC
37BDBABBD
3926%29%16%16%29%26%
Main
Cell Formulas
RangeFormula
C39:H39C39=COUNT(FILTER(ROW(C1:C38),C1:C38=\$B\$1:\$B\$38))/COUNTA(C1:C38)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:H38Expression=C1=\$B1textNO

#### Redbull753

##### New Member
Thank you very much! that works perfectly!!!

#### Redbull753

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

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
17
Views
135
Replies
0
Views
93
Replies
2
Views
69
Replies
11
Views
244
Replies
0
Views
101

1,127,730
Messages
5,626,559
Members
416,190
Latest member
plee3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back