Nested Criteria on COLLECT

Ardneran

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"))

Ardneran

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.

Jeff Reisman

@Ardneran

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?

Paul Newcome

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

Ardneran

@Jeff Reisman

I have no idea how to simplify the date criteria, the additional parentheses also doesn't seem to work

Ardneran

@Paul Newcome

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.

Ardneran

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.