Using COUNTIFS and OR statement

ErinM · 02/24/23

You're all so amazingly helpful that I'm back. I think this is an easy fix for most, but I'm scratching my head.

I have a column named Status with multiple drop down options, of which only one can be used. I also have a column named PMO that is a checkmark.

I want to get a count of PMO flagged rows that fall within several statuses (planning, execution, monitoring). The formula below works for one status - how do I sneak in the OR to pick up each of those three?

=COUNTIFS(Status:Status, "Planning", PMO:PMO, 1)

Thanks!

Jeff Reisman · 02/24/23

@ErinM You are on the right track with OR.

=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))

Jeff Reisman · 02/24/23

@ErinM You are on the right track with OR.

=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))

ErinM · 02/24/23

Thank you so much, @Jeff Reisman - that worked! How on Earth do you all know how to do this stuff so well?!?

Jeff Reisman · 02/24/23

https://community.smartsheet.com/discussion/comment/364271#Comment_364271

😂 4 years experience with it, I find it interesting, and I have one of those inductive-thinking brains (ADHD superpower), plus solving small problems like this keeps the dopamine coming. 🤪

Jeff Reisman · 02/24/23

@ErinM I think you've accidentally made this post three times. Might want to edit the others to erase the text and mark as duplicate.

ErinM · 02/24/23

Ugh, yes, I was having an issue earlier. I'll fix that, thank you!

So, @Jeff Reisman - I'm trying to marry two different types of formulae that I learned through the community's help!

Two dropdowns, each with one selection option only. I want the Sponsor Division to contain "OneIT" and choose from one of the statuses in Status. Thoughts on why this is breaking?

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, (@cell = "planning", @cell = "execution", @cell = "monitoring", @cell = "hold"))

Jeff Reisman · 02/24/23

@ErinM I typed the below and then looked closer: You left the OR out!

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring", @cell = "hold"))

Are you getting an error message (and if so, which one,) or just a 0 count when you know the values are there?

Is the value you're looking for in Sponsor Division column exactly = "OneIT", or is "OneIT" just a part of the cell value? CONTAINS searches for string values within cells, so it would match both "OneIT" and "OneIT-TwoIT-ThreeIT-Four". If you're only interested in cells where the value equals "OneIT" exactly, you don't need CONTAINS.

Using COUNTIFS and OR statement

Best Answer

Answers

Help Article Resources

Categories