Count "text" if the status is "Doing"

I'd like a way to count Theme column (Text Column) if "IVR" appears, AND if the Status Column (Dropdown column) is either "Backlog" or "To Do" or "Doing"

I can get a simple countif formula to work, but can't figure out how to add a second criteria based on status

i tried ones along the lines of

=COUNTIF(AND(Theme:Theme, "IVR"), IF(Status:Status, "Doing"))

=COUNTIFS(Theme:Theme, ="IVR", Status:Status, ="To Do", "Doing", "Backlog")

Best Answers

  • Angela Ryer
    edited 01/16/20 Answer ✓

    Yes, thank you. Using yours as a base, I was able to tweak it a bit and remove each "Theme:Theme, "IVR" section; still have it work.

    =SUM(COUNTIFS(Theme:Theme, "IVR", Status:Status, "Backlog"), COUNTIFS(Status:Status, "To Do"), COUNTIFS(Status:Status, "Doing"))

    Then that got me thinking further to attempt to simplify it with @cell - which worked as well

    =COUNTIFS(Theme:Theme, "IVR", Status:Status, OR(@cell = "Backlog", @cell = "To Do", @cell = "Doing"))

    Thanks much; What you came up with helped me have a jumping off point. I was going in circles before.

Answers

  • Angela Ryer
    edited 01/16/20 Answer ✓

    Yes, thank you. Using yours as a base, I was able to tweak it a bit and remove each "Theme:Theme, "IVR" section; still have it work.

    =SUM(COUNTIFS(Theme:Theme, "IVR", Status:Status, "Backlog"), COUNTIFS(Status:Status, "To Do"), COUNTIFS(Status:Status, "Doing"))

    Then that got me thinking further to attempt to simplify it with @cell - which worked as well

    =COUNTIFS(Theme:Theme, "IVR", Status:Status, OR(@cell = "Backlog", @cell = "To Do", @cell = "Doing"))

    Thanks much; What you came up with helped me have a jumping off point. I was going in circles before.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!