google sheets - Highlight Duplicate row with googlespreadsheet

Question

I'm trying to highlight the duplicate row for the following table. The purpose is to highlight the student that is absent in more than 2 days using Google Spreadsheet only. The data already modified and only captures a few of them.

        |------------|-------------|-------------|-------------|------------|
        |    Day 1   |   Day 2     |    Day 3    |    Day 4    |   Day 5    |
        |------------|-------------|-------------|-------------|------------|
        |John        |John         |Dandy        |Yuri G       |John        |
        |Apppleton   |Appleton     |Appleton     |Anton Doyle  |Appleton    |
        |Yuri G      |Chris        |John         |Appleton     |Yuri G      |
        |Anton Doyle |Anton Doyle  |Anton Doyle  |Asanka       |Anton Doyle |
        |Boyle       |Boyle        |Yuri G       |Reeta        |Asanka      |

This is the expected output

The criteria is, if the student absent for 2 days, the highlight will turn green. If 3 days, it will highlight yellow. If 4 days or more will highlight red. is this possible using conditional formatting in google spreadsheet? I have tried =countif(A:C,"John")>2 for more than 3 days. However the formula highlights all of the column in A:C . Kindly expect any recommendation for this. thanks

Answer 1

I believe, the Google Spreadsheet by default stops if any one criteria meets.

You can Click Here to check the formula.

Click on the cell A2 and enter the below formulas in the given order and select corresponding color. Use 'Add Rule' to add each of these rules.

=COUNTIF($A$2:$E$100,A2)>3
=COUNTIF($A$2:$E$100,A2)>2
=COUNTIF($A$2:$E$100,A2)>1