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!

Find more posts tagged with

Formulas

Accepted answers

All comments

egold31

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.

rrenee

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.