Using NOT with JOIN/COLLECT
Hello Everyone!
I am creating a formula that returns the ID if the item type is "Book" and the status is NOT "Sold". My current formula I was trying is:
=JOIN(COLLECT({ID}, {Item}, "Book", NOT({Status}= "Sold"), CHAR(10))
What is the correct way to return the ID for an item that is "Book" and the status is NOT "Sold". (Or the status is "Available" or "Pending"?
Thank you!
Answers
-
Additionally, how do you add conditionals (OR/AND/NOT) into a COUNTIFS statements to see how many entries meet a specific criteria?
My questions is how do you structure COUNTIFS when it has to meet 3+ conditions? Or use NOT to simplify it.
-
Hello!
I believe the way to do this is use the "not equal to" sign, which is this: <>. Thus, for your collect statement, write it as so:
=COLLECT({ID}, {Item}, "Book", {Status}, <> "Sold")
Logically, this will collect ID when Item is book and status is not equal to sold.
Here is the combined:
=JOIN(COLLECT({ID}, {Item}, "Book", {Status}, <> "Sold"),CHAR(10))
Just like collect, you can use <> to filter the criteria. For layering some OR statements within COUNTIFS, you can do something like this:
=COUNTIFS({Column Desired}, {Criteria 1 Range}, OR(@cell = "A", @cell = "B"), {Criteria 2 Range}, <> 1)
I hope this helps!
Renée Roberge
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 217 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!