Using an OR in COLLECT

Hello -

I'm trying to use an OR operator in the criteria of a collect function. This is currently returning an #INCORRECT ARGUMENT SET:

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))

I'm trying to return the a deposit number {WAP Deposit Number} if the Date matches and the type is EITHER "Check" or "Credit"

It's probably something really dumb I'm missing...

Any ideas?

Thanks,

Andy

Find more posts tagged with

Formulas

IT & Operations

Comments

Andrée Starå

Hi Andy,

This might help!

https://help.smartsheet.com/function/or

Did it help?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andy_DAI

Hi Andree -

Thanks - Unfortunately, the syntax is a bit different in the collect function.

It's COLLECT(RANGE, Criterion_range1, criterion1, etc.)

Paul Newcome

Use @cell references. The OR function is looking for logical statements.

..............OR(@cell = "This", @cell = "That")..............

Andrée Starå

Happy to help!

I know that, but I didn't have time to write it out before.

Paul Newcome

I was just looking at this again, and I realized that your parenthesis are also just a little off..

Here is your original formula:

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))

The parenthesis after "CREDIT" is closing out the OR.

This means that the 1 is actually included in the COLLECT statement (as a range which doesn't work and also happens to be missing a criteria). This pushes "" to your row number in your INDEX function which means that nothing is specified for the second portion of your IFERROR statement.

All of these things on their own will cause the #INCORRECT ARGUMENT SET error.

Add in a closing parenthesis after "CREDIT", and remove one form the end of the formula.

This along with the @cell references in the OR function should clear everything up.

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR(@cell = "CHECK", @cell = "CREDIT")), 1), "")

Paul Newcome

EDIT to above (cloudfare blocking issues again):

I also wanted to apologize for missing this earlier. I was so focused on the title of your post trying to use an OR within a COLLECT that I didn't look at the formula as a whole in reference to the error message you were getting.

Let us know if this helps.

Ross Novotny

@Paul Newcome I have a very similar formula I am trying to write. I am looking to for an index/Collect formula that will pull in a result for either "Location Status'" of "Location Recommended" or "Location Approved". Where did I screw this up?

=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, "Location Recommended", OR({Location Status}, "Location Approved")), 1), "")

Paul Newcome

@Ross Novotny Try this one...

=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, OR(@cell = "Location Recommended", @cell = "Location Approved")), 1), "")

ssagers

@Paul Newcome & @Andrée Starå - Just wanted to thank you for helping so much in these forums! Yet again you gave me an answer. <3

Sign in to join the conversation!

Quick Links

Trending discussions

Parent/Children row date automation
I'm looking for some help with a possible parent/child formula. We have a parent row that lists the project name, then two date columns - start date and end date. The children rows list the tasks that have to be completed for the project. Each of the tasks will have a different start date and end date. The Foreman is…
Creating a formula that takes the total average of a row but excludes certain entries
I need to create a formula that take the total average of the row but doesnt include entries that are "N/A" "YES" or "NO" in the count. At present when ever those are entered the formula im using counts as "0" which greatly decreases the total percentage
HELP WITH INDEX/MATCH ACROSS MULTIPLE COLUMNS WITH CRITERIA
Looking for formula help to accomplish the example below: