Count occurrences and returns color

%3CLINGO-SUB%20id%3D%22lingo-sub-2769707%22%20slang%3D%22en-US%22%3ECount%20occurrences%20and%20returns%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769707%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20a%20formula%20or%20a%20VBA%20script%20%3F%3CBR%20%2F%3EThere%20are%204%20columns%20with%20many%20possible%20choices%20(Alice%2CBob%2CClarisse%2CDenis%2CErnest...).%20The%20whole%20idea%20is%20to%20let%204%20teams%20(columns)%20to%20pick%20a%20name%20as%20representative%20and%20check%20if%20they%20do%20agree%20on%20a%20name%20or%20not%3CBR%20%2F%3EI%20would%20like%20to%20count%20the%20number%20of%20name%20and%20return%20a%20color.%3CBR%20%2F%3EFor%20instance%2C%20if%20in%20a%20row%2C%20there%20are%204%20%22Alice%22%20or%203%20%22Alice%22%2C%20it%20should%20return%20green%20color%20cell%3CBR%20%2F%3EIF%20there%20are%20only%202%20%22Alice%22%2C%20it%20returns%20orange%20color%20cell%3CBR%20%2F%3ETo%20finish%20with%2C%20if%20a%20row%20contains%20less%20that%20one%20%22Alice%22%2C%20it%20will%20return%20a%20red%20color%20cell.%3C%2FP%3E%3CP%3EI%20tried%20some%20formulas%20with%20%22IF%22%20and%20%22CountIF%22%2C%20but%20cannot%20apply%20it%20to%20the%20whole%20sheet%20since%20the%20names%20are%20all%20different%20(mostly%20names).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22VincentDraghi_0-1632213110640.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311755i724FEA8FBF7DE468%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22VincentDraghi_0-1632213110640.png%22%20alt%3D%22VincentDraghi_0-1632213110640.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20colored%20cell%20goes%20to%20the%20corresponding%20row%20from%20the%20table%20%22Result%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2769707%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769931%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20occurrences%20and%20returns%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F959151%22%20target%3D%22_blank%22%3E%40VincentDraghi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShould%20this%20be%20specific%20for%20Alice%2C%20or%20do%20you%20want%20to%20color%20a%20cell%20green%20if%20any%20name%20gets%203%20or%204%20votes%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2770041%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20occurrences%20and%20returns%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2770041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20!%3C%2FP%3E%3CP%3EYes%20this%20is%20not%20specific%20to%20Alice%20but%20any%20name%20since%20there%20are%2033%20different%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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 !