Can COLLECT Criteria use text values?

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!

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.