Nested Criteria on COLLECT
I'm trying to get an income by multiplying quantity with Price. Price is located on another sheet so I have to find it based on type, route, and active date. Here's the formula that I have:
=IFERROR([Economy QTY]@row * (INDEX(COLLECT({Price Range}; {Type Range}; "Economy"; {Route Range}; CONTAINS(Route@row; @cell); {Active Date Range}; MAX(COLLECT({Active Date Range}; {Active Date Range}; <=(Date@row); {Route Range}; CONTAINS(Route@row; @cell); {Type Range}; "Economy"))); 1)); "No ticket data")
Unfortunately the formula gives a #NESTED CRITERIA error, I think there's a problem with the active date criteria. In the price sheet, there are many prices for the same route and type but they will have different active date. I have to choose the latest date before the row's date. I don't know what I'm missing here.
Many thanks for the help.
Edit : If I separate this part on to another column and then refer it back, it works. But I don't want to add another column
MAX(COLLECT({Active Date Range}; {Active Date Range}; <=(Date@row); {Route Range}; CONTAINS(Route@row; @cell); {Type Range}; "Economy"))
Best Answer
-
Update :
I think I figured it out, the formula has two @cells so I think there might be some inconsistency. I changed how my sheet works by modifying some columns so I don't have to use @cell. Thank you all.
Answers
-
You've got a lot going on in there. Since you know the issue is with using the MAX/COLLECT as criteria, have you tried to simplify how you're collecting that date? Or maybe try wrapping the whole MAX/COLLECT inside its own set of parentheses?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Since you are already including the route and type in the original COLLECT, you shouldn't need duplicate it. It should work if you just say
{Date Range}; @cell<= Date@row
-
I have no idea how to simplify the date criteria, the additional parentheses also doesn't seem to work
-
That removes the error but the output is not what I wanted. If I only include date criteria, it returns the latest date whether the latest date have the price for the corresponding type and route or not.
-
Update :
I think I figured it out, the formula has two @cells so I think there might be some inconsistency. I changed how my sheet works by modifying some columns so I don't have to use @cell. Thank you all.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!