Calculating Critical Open Items per Business Analyst

Cayla Davis

I am trying to calculate how many Open items a business analyst has that are in the critical category, this is the formula i am trying to use but i must be doing something wrong

Open equals = To Do, In Progress, Daily Task

=COUNTIFS({2025 Team Activity Tracker Range 3}, HAS(@cell, "BA NAME"), {2025 Team Activity Tracker Range 1}, @cell = Status13), OR(HAS(@cell, Status2), (HAS(@cell, Status3), (HAS(@cell, Status4), (HAS(@cell, Status5)))))

Paul Newcome

You would need something more along the lines of:

=COUNTIFS({Analyst Column}, HAS(@cell, "BA NAME"), {Status Column}, @cell <> "Completed", {Category Column}, @cell = Status@row)

Note: You indicated in your text that the "Open" statuses are To Do, In Progress, and Daily Tracker, but in your formula you also included Deferred. Instead of saying one of these four statuses, I specified not Completed. If you also need to exclude Deferred, two statuses to exclude is still less than three statuses to be included, so I would go with exclusion instead of inclusion.

=COUNTIFS({Analyst Column}, HAS(@cell, "BA NAME"), {Status Column}, AND(@cell <> "Deferred", @cell <> "Completed"), {Category Column}, @cell = Status@row)

Paul Newcome

You would need something more along the lines of:

=COUNTIFS({Analyst Column}, HAS(@cell, "BA NAME"), {Status Column}, @cell <> "Completed", {Category Column}, @cell = Status@row)

Note: You indicated in your text that the "Open" statuses are To Do, In Progress, and Daily Tracker, but in your formula you also included Deferred. Instead of saying one of these four statuses, I specified not Completed. If you also need to exclude Deferred, two statuses to exclude is still less than three statuses to be included, so I would go with exclusion instead of inclusion.

=COUNTIFS({Analyst Column}, HAS(@cell, "BA NAME"), {Status Column}, AND(@cell <> "Deferred", @cell <> "Completed"), {Category Column}, @cell = Status@row)

Cayla Davis

Perfect! That worked, thank you so much!!! You taught me something new :) Thanks Paul

Paul Newcome

Happy to help. 👍️