COUNTIF with OR
I am trying to count all instances of a name where the corresponding status is Open or Tracking.
My formula is =COUNTIFS({Coordination Log - Autumn Willow Range 1}, "Chris Rush", {Coordination Log - Autumn Willow Range 2}, OR(@cell = "Open", @cell = "Tracking"))
In the last portion where the OR comes into play, I initially tried having the range {Coordination Log - Autumn Willow Range 2} = to "Open" and "Tracking" but that yielded #INVALID OPERATION.
My current fomula should output a value of 4, but I am getting 0. Is this perhaps a syntax error where I have @cell = "Open"/"Tracking" ?
NOTE: I am trying to count the name within a multi-select contact column. Will I need to use a COUNTM?
Best Answer
-
You need to use the HAS function to check if the column has the name Chris Rush:
=COUNTIFS({Coordination Log - Autumn Willow Range 1}, HAS(@cell, "Chris Rush"), {Coordination Log - Autumn Willow Range 2}, OR(@cell = "Open", @cell = "Tracking"))
Answers
-
You need to use the HAS function to check if the column has the name Chris Rush:
=COUNTIFS({Coordination Log - Autumn Willow Range 1}, HAS(@cell, "Chris Rush"), {Coordination Log - Autumn Willow Range 2}, OR(@cell = "Open", @cell = "Tracking"))
-
@Leibel S You my good friend are brilliant. It works! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!