Hi,

I am trying to figure out a way to find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:

Capture.PNG

  1. I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.
  2. I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.

I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?

Formula: copy to clipboard 
=INDEX(B2:B16,MATCH(MAX(COUNTIF(B2:B16,B2:B16)),COUNTIF(B2:B16,B2:B16),0))

Pardon by phraseology, I'm not sure if "lookup value" is the correct term to use here, but I hope I explained my question well.

Thank you.