Excluding multiple options from a COUNTIF..., istext@cell
I have a column where I want to count any entry which I used a , istext(@cell), however now there are entries that I want to exclude, I used the formula below for my Sheet Summary 'Count of Routes'
=COUNTIF(Route:Route, ISTEXT(@cell)) - COUNTIF(Route:Route, "AVA IC / Dispatch")
But now I am getting additional entries that I need to not count, "Grabb", "SIT Safety" there will probably be more. Is there a way to group all of the exclusions?
Co
Best Answers
-
There are two ways to do this depending on which list is shorter. Those to Exclude or Those to Include.
Exclude List:
=COUNTIFS(Route:Route, AND(@cell <> "A", @cell <> "B", @cell <> "C"))
Include List:
=COUNTIFS(Route:Route, OR(@cell = "D", @cell = "E", @cell = "F"))
-
Thanks @Paul Newcome , I will try one of those options, better then my current solution..
=COUNTIF(Route:Route, ISTEXT(@cell)) - (COUNTIF(Route:Route, "AVA IC / Dispatch") + COUNTIF(Route:Route, "Grabb") + COUNTIF(Route:Route, "SIT Safety"))
-
Happy to help. 👍️
Answers
-
There are two ways to do this depending on which list is shorter. Those to Exclude or Those to Include.
Exclude List:
=COUNTIFS(Route:Route, AND(@cell <> "A", @cell <> "B", @cell <> "C"))
Include List:
=COUNTIFS(Route:Route, OR(@cell = "D", @cell = "E", @cell = "F"))
-
Thanks @Paul Newcome , I will try one of those options, better then my current solution..
=COUNTIF(Route:Route, ISTEXT(@cell)) - (COUNTIF(Route:Route, "AVA IC / Dispatch") + COUNTIF(Route:Route, "Grabb") + COUNTIF(Route:Route, "SIT Safety"))
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!