Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
COUNTIF USING OR
How do I add an OR to the expression and logic:
="Resource: " + COUNTIF(CHILDREN(Status1:Status17), "Terminal Updated") + " of " + COUNT(CHILDREN(Status1:Status17))
I want to add both Terminal Updated and Terminal Replacement to the count.
Any help greatly appreciated!
Comments
-
Hi Tee,
If you're looking to have your COUNTIF count anything that meets one of multiple criteria, then you can use a formula like the following example:
=COUNTIF(CHILDREN([Status1:Status17), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))
You'll want to replace your current COUNTIF with this.
One other note on the CHILDREN() function is that if your formula is in the parent and it's referencing its direct children (cells right underneath it) you don't need to add cell references, e.g. =COUNTIF(CHILDREN(), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))
If you want the formula to reference another parent's children, you can just reference the parent cell, e.g. = COUNTIF(CHILDREN(Status1), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))
-
Thanks, I will try it
-
This does not actually work for me.
I've published an example here if I can get some support getting it working. Screenshots attached. Error is #Invalid Operation.
https://app.smartsheet.com/b/publish?EQBCT=491449d252c04838be1208fe5cd9c5f8
Josh
-
Responding to Josh:
Your syntax is wrong. Your use of the [Type] column implies you are looking for something there (car, wagon, etc...) but you aren't.
I fixed the formula on your sheet:
=COUNTIF(Color1:Color11, OR(@cell = "Red", @cell = "Yellow"))
If you are wanting to use TYPE, use COUNTIFS
I hope this helps
Craig
-
And that's why J. Craig is the man.
-
You are welcome.
Craig
-
Hello everyone,
I am looking to have an information sheet that is looking for several criteria with "and" and "or".
For example, I want the formula to count if the other sheet has "Priority 1" and "John Doe" and a status of either 1 or 2 or 3.
I will have another cell on the information sheet for "Priority 2" and "John Doe" and a status of either 1 or 2 or 3.
I am not using children and the sheet I am referencing is one that I am shared with as Editor - cannot share, in case that is an issue.
Thank you.
Sincerely,
Diego
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K 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