Countifs counting this not in criteria

I am using [Method helper] to tell me when we have more than one online training on a date where Notes= PD

=COUNTIFS(Notes:Notes, "PD", [Date 1]:[Date 1], [Date 1]@row, Platform:Platform, Platform@row, Platform:Platform, "online") > 1

I have tried to specifically say, do not count anything with CDA in it, in a variety of ways. If you look above there is one case where it flags a non PD, and one where it does not. If you look through out the entire sheet, there are other instances where it will flag or not flag a non PD training. Some have more than three total online trainings on a day, some have only 2. I have tried deleting the formula, saving, refreshing, and then reinputting the formula, hoping the sheet was just not feeling it. Not the case. Any insight would be greatly appreciated.


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @ECRCalexandra,

    If you're not wanting to flag the non-PD lines, then use something along the lines of this:

    =IF(AND(Notes@row = "PD", COUNTIFS(Notes:Notes, "PD", [Date 1]:[Date 1], [Date 1]@row, Platform:Platform, Platform@row, Platform:Platform, "online") > 1), 1, 0)

    This does the checks, but makes sure the line is PD before ticking/not.

    Hope this helps, but if you've any problems/questions or I've misunderstood something then just post! 🙂


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!