Using COUNTIFS and OR statement
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!
Best Answer
-
@ErinM You are on the right track with OR.
=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@ErinM You are on the right track with OR.
=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you so much, @Jeff Reisman - that worked! How on Earth do you all know how to do this stuff so well?!?
-
😂 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. 🤪
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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"))
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!