Count occurrences and returns color

New Contributor

Hello

Can anyone help me with a formula or a VBA script ?
There are 4 columns with many possible choices (Alice,Bob,Clarisse,Denis,Ernest...). The whole idea is to let 4 teams (columns) to pick a name as representative and check if they do agree on a name or not
I would like to count the number of name and return a color.
For instance, if in a row, there are 4 "Alice" or 3 "Alice", it should return green color cell
IF there are only 2 "Alice", it returns orange color cell
To finish with, if a row contains less that one "Alice", it will return a red color cell.

I tried some formulas with "IF" and "CountIF", but cannot apply it to the whole sheet since the names are all different (mostly names).

 

VincentDraghi_0-1632213110640.png

 

The colored cell goes to the corresponding row from the table "Result"


Thanks in advance

4 Replies

@VincentDraghi 

Should this be specific for Alice, or do you want to color a cell green if any name gets 3 or 4 votes?

@Hans Vogelaar 

 

Hello !

Yes this is not specific to Alice but any name since there are 33 different names.

@VincentDraghi 

 

Let's say  that the choices are in columns A to D, starting in row 2 and that you want to color F2 and down.

Select the range in column F that you want to format. F2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))>=3

 

Click Format...

Activate the Fill tab.

Select green.

Click OK, then click OK again.

 

Repeat these steps, but with the formula

 

=COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))=2

 

and orange.

Finally, repeat them again with the formula

 

=AND(COUNTA(A2:D2),COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))=0)

 

and red.

Thanks a lot, i will try it out !