1

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?

1 Answer 1

2

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")).

2
  • You Method works for numbers, but fails to count matching text. How do I alter it to recognize Text? I want it to count how many times "CCH" is listed in those cells for example, even if the cells contain text like "O: CCH" or "P: CCH".
    – ITguy211
    Nov 3, 2015 at 15:55
  • Which part isn't working for you? I tested using the text values from your comment and it's working for me. If it's the IF wrapper, you need to replace the AB10 value with the CountIF formula. Is it giving you an error value (if so, which one)? Or is it just blank (or something else)?
    – dav
    Nov 3, 2015 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.