Can COLLECT Criteria use text values?

twarner

I am trying to find the earliest date in a range of dates. But I only want to consider dates that have a certain text value in a corresponding cell.

Here is the formula I'm trying to get to work:

=MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]@row:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]@row), ="ON/LIVE")

[Prod Date [n] (copy)] range are dates

[Prod Date [n] - (ON / OFF / DARK / LIVE) (copy)] range are text

I get an "#INCORRECT ARGUMENT SET" error with this formula.

Would appreciate any help on what I'm doing wrong here.

Thanks!

Randy Saad

You have it, but you are using two @row ranges referencing different rows. If you your formula is in the row of dates and the row containing your "ON/LIVE" values is row 4, your formula would be:

=MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]4:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]4), ="ON/LIVE")

twarner

Hi Randy -

Thanks for your answer. However, ALL of the data lives on a single row. This is a sheet of project status and each row represents a Project. Each project row has 8 possible deployment dates and each deployment date has a "type" field. So I am trying to find the earliest deployment date with the "ON/LIVE" deployment "type".

Hopefully that makes sense. Perhaps it's because everything is on the same row that the formula is puking on me?

Thanks,

Tony