determine percentage of values matching another column

Redbull753

New Member
Joined
Dec 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
Excel2.png
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,103
Office Version
  1. 365
Platform
  1. Windows
Cell Formulas
RangeFormula
B1:H38C1=CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")
C39:H39C39=SUMPRODUCT(--(C1:C38=B1:B38))/COUNTA(C1:C38)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff v2.xlsm
BCDEFGH
1CCDBAAC
2BADCABC
3ADDBDAB
4ACCBAAA
5BAADDDC
6ABBDBAB
7BDADAAA
8CCBADAB
9CCBBACB
10DACCACD
11CCBADDB
12DCADDCB
13BBBCDAA
14CBDCDDB
15ABADCDC
16BAABDAB
17CDCABCC
18ADCACCC
19AACDBAA
20CABAADC
21BCBBDBB
22DBCBDCB
23BAAADDD
24DDAAABA
25ABDCCCA
26DBABCBD
27DDBABAB
28BCAADAC
29BDBCAAA
30ADADBAC
31CADAABB
32DCACACB
33AAACDAD
34ADCCADB
35DDDBAAC
36DADDDCB
37BDBABBD
38BCBADAC
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
Joined
Dec 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
This forum is the best. A real life saver. Many thanks to everyone who helps out!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,758
Messages
5,626,683
Members
416,200
Latest member
Pulsar3000

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?

Disable AdBlock

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
Top