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

How to pull one column of data from one sheet to another and be placed in the corresponding box?
Hello, I have one sheet where information is collected for candidates we are looking to hire, and I need a specific column from another sheet of information to feed over to the candidate sheet in the column. The results that are fed over, must align with the correct candidate. Pictures below for reference. I tried…
OTIF
I am trying to calculate OTIF per month. I just added a Helper Column to identify Ontime and Late items. For example, would like to know Aug. 2025 On Time vs Sept. 2025 On Time. How can I achieve this?
Average Collect Unparseable Error
I'm getting an Unparseable error with my average(collect) formula and can't figure out what's wrong with it. =average(Collect({Days to Approval}, {Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row )) All the references within "{}" brackets are columns…