COLLECT with an OR criteria

Overachievers
edited 02/16/23

I have a COLLECT statement with multiple criterias, one being and involving an OR. In the below formula I have a Discipline column where I want to bring back Reading or Literature for a particular year.

=JOIN(COLLECT({SA Grid: ELA}, {SA Grid: Go/NoGo}, ="[X]", {SA Grid: Year}, =Year@row, {SA Grid: Discipline}, OR(="Reading", ="Literature"), CHAR(10) + "==============" + CHAR(10)))

Potentially, the result for 2023 could look something like... because both Reading and Literature would be considered under the ELA discipline

paul e. reeves

Principal Business Analyst

HMH

Best Answer

• Overachievers
Answer ✓

@Samuel Mueller - That did it. I think I was so far into the weeds and brain dead that I was not seeing that I had not closed out the COLLECT.

paul e. reeves

Principal Business Analyst

HMH

Answers

• Overachievers

@Paul Reeves try adding @cell in your or criteria

=JOIN(COLLECT({SA Grid: ELA}, {SA Grid: Go/NoGo}, ="[X]", {SA Grid: Year}, =Year@row, {SA Grid: Discipline}, OR(@cell="Reading", @cell="Literature"), CHAR(10) + "==============" + CHAR(10)))

• Overachievers

@Samuel Mueller thanks for the suggestion however I am still getting the Incorrect Argument Set.

paul e. reeves

Principal Business Analyst

HMH

• Overachievers

@Paul Reeves try taking off one of the last parenthesis

=JOIN(COLLECT({SA Grid: ELA}, {SA Grid: Go/NoGo}, ="[X]", {SA Grid: Year}, =Year@row, {SA Grid: Discipline}, OR(@cell="Reading", @cell="Literature"), CHAR(10) + "==============" + CHAR(10))

Let me know if that works ^

• Overachievers

@Samuel Mueller No that does not seem to solve it. The formula keeps putting on the outside parenthesis for the JOIN function. There should be 3 functions and 3 sets of parenthesis: JOIN, COLLECT, OR.

Seems like it should work though.

paul e. reeves

Principal Business Analyst

HMH

• Overachievers

@Paul Reeves Thank you you are correct, you need the second parenthesis after the or to finish the collect formula, like this

=JOIN(COLLECT({SA Grid: ELA}, {SA Grid: Go/NoGo}, ="[X]", {SA Grid: Year}, =Year@row, {SA Grid: Discipline}, OR(@cell="Reading", @cell="Literature")), CHAR(10) + "==============" + CHAR(10))

Try that one

• Overachievers
Answer ✓

@Samuel Mueller - That did it. I think I was so far into the weeds and brain dead that I was not seeing that I had not closed out the COLLECT.

paul e. reeves

Principal Business Analyst

HMH

• Overachievers

I missed it too! Glad you got it working.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!