Formula to count multiple columns with multiple drop-down options

Options

Hello,

I'm trying to develop a formula to count the number of issues being tracked by a priority, by a status, and by a submitting team. For example, I want to count the number of items have a priority of "Low" (in the priority column), a status of either "Open", "On Hold" (status column), or "Escalated", and was submitted by "Outreach" (Submitting ONDT Team column).

So far, I've gotten this formula to work but it only counts one of the three types of statuses rather than all three:

=COUNTIFS({Operational Issue/Request Tracker (Client Range 1}, "Low", {Operational Issue/Request Tracker (Client Range 2}, "Outreach") + COUNTIFS({Operational Issue/Request Tracker (Client Range 3}, "Open" + "On Hold" + "Escalated")

This will return a count but I think it's only counting if the status has "Open". I have two items, one that is "Open" and one "On Hold" but it is only showing "1" in the count, so I'm not sure which one it is counting but I do know it isn't counting accurately/how I want it to.

Any suggestions is appreciated!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @ANTHONY MARELLI

    Try this instead:

    =COUNTIFS({Operational Issue/Request Tracker (Client Range 1}, "Low", {Operational Issue/Request Tracker (Client Range 2}, "Outreach", {Operational Issue/Request Tracker (Client Range 3}, OR(@cell = "Open", @cell = "On Hold", @cell = "Escalated")


    You only need the one COUNTIFS so that it filters the row by all three criteria. Let me know if it works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @ANTHONY MARELLI

    Try this instead:

    =COUNTIFS({Operational Issue/Request Tracker (Client Range 1}, "Low", {Operational Issue/Request Tracker (Client Range 2}, "Outreach", {Operational Issue/Request Tracker (Client Range 3}, OR(@cell = "Open", @cell = "On Hold", @cell = "Escalated")


    You only need the one COUNTIFS so that it filters the row by all three criteria. Let me know if it works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!