Can a function be used to alert if values are present in a table more than twice or not at all?

Question

I am trying to help someone in my office with a surgery schedule spreadsheet. She would like a formula that will check a group of cells for a set of initials, and be alerted if the initials are present more than twice, or if they are not present at all. I have researched and tested, but I cannot achieve this goal with my limited excel knowledge.

Precisely what we are trying to do is:

• Check Cells F17:J36 to see if ANY ONE of the Values from Cells AA10:AA28 is listed MORE THAN TWICE
• Check Cells F17:J36 to see if ANY ONE of the Values from Cells AA10:AA28 is MISSING

I have found functions like ISNUMBER and COUNTIFS but I cannot wrap my head around how to accomplish the above with them.

Is this possible to do with regular Excel Functions? If yes, how can I accomplish this?

Answer 1

In some cell (probably AB10, adjacent to AA10), enter the following formula =countif($F$17:$J$36,AA10). This will count all values in the range F17:J36 that matches the value in AA10.

Fill this formula down to AB28 (or its equivalent range). You'll get a result of 0-x (where x is the total count of that value in the range).

To highlight everything other than count=1, you can use conditional formatting.

Or, you can wrap your formula in a =IF statement that will provide a more user-friendly message, =IF(AB10=0,"None",IF(AB10="","More than 1")).