Using NOT with JOIN/COLLECT

egold31

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!

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!