Countifs counting this not in criteria

ECRCalexandra

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.

Nick Korna

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! 🙂

Nick Korna

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! 🙂

ECRCalexandra

That did the trick. I took out the PD restriction under the countifs. Thank you!!

ECRCalexandra