Nested Criteria on COLLECT

Ardneran
Ardneran ✭✭
edited 12/22/22 in Formulas and Functions

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

  • Ardneran
    Ardneran ✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!