MAX and COLLECT to find the latest date with specific criteria?

Hi @Van Keuren, Jeremy

You are very close! Yes, a MAX(COLLECT would be an ideal formula for this. However, since this is not a part of an INDEX(MATCH or any other formula, you don't need the 1 or the extra closing parentheses at the end.

Try this:

=MAX(COLLECT({Stage 3: Complete Range 1}, {Stage 3: Complete Range 2}, "Bob's Chicken Farm"))

You only need 2 closing Parentheses (one for the MAX, one for the COLLECT). Let me know if this works for you!

Keep in mind that if your Created Date column is a Date Type of column, this formula will also need to be placed within a Date Type of column in the destination sheet, as well.

If you're receiving an error, let us know what it is so we can help troubleshoot further.

Cheers!

Genevieve

All comments

Hi @Van Keuren, Jeremy

Try this:

=MAX(COLLECT({Stage 3: Complete Range 1}, {Stage 3: Complete Range 2}, "Bob's Chicken Farm"))

You only need 2 closing Parentheses (one for the MAX, one for the COLLECT). Let me know if this works for you!

Keep in mind that if your Created Date column is a Date Type of column, this formula will also need to be placed within a Date Type of column in the destination sheet, as well.

If you're receiving an error, let us know what it is so we can help troubleshoot further.

Cheers!

Genevieve

Van Keuren, Jeremy

Ooops, yep, that there did it. I also had my ranges switched around. Thanks!

No problem! Glad you were able to get it working 🙂

I am trying to accomplish something similar to this.

I have a sheet that the Account name column, status of customer in "Customer 12 Month Outlook" Column, and a Last Modified date column. I want to then pull the "Customer 12 month outlook" for each Account into another spreadsheet. Every quarter The "Customer 12 month outlook" is entered as a new row, so there may be multiple rows for one account to search for latest modified by date and pull back the associated customer 12 month outlook into.

would a MAX(COLLECT()) work here as well?

The most recent 12month outlook (based on last modified date) from the above sheet, should populate in the "latest 12 month customer outlook" in the sheet here based on the associated Account Name

See: Formula combinations for cross sheet references

Yes! I would use both MAX and COLLECT functions here, however you would want these in an INDEX as well, since you're pulling back text and not the date (you're using the date as a criteria).

For example:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, "Date"), 1)

However, since we're looking for the MAX date but from a specific criteria, we'll need to us a MAX(COLLECT inside of this formula as well:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, MAX(COLLECT({Date Column}, {Criteria Column}, "Criteria 1"))), 1)

So in your case, something like this:

=INDEX(COLLECT({Customer 12 Month Column}, {Account Column}, [Account Name]@row, MAX(COLLECT({Date Column}, {Account Column}, [Account Name]@row))), 1)

Let me know if this works for you!

Cheers,

Genevieve

Hi @Genevieve P. thanks for the quick response!

I tried your suggestion but am receiving an error: #INCORRECT ARGUMENT SET

=INDEX(COLLECT({SFA_SFDC_Scorecard Range 2_12 month outlook}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row, MAX(COLLECT({SFA_SFDC_Scorecard Range 1 (Last Modified)}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row))), 1)

My apologies! I missed adding the range you're looking into before the MAX(COLLECT.

=INDEX(COLLECT({SFA_SFDC_Scorecard Range 2_12 month outlook}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row, {SFA_SFDC_Scorecard Range 1 (Last Modified)}, MAX(COLLECT({SFA_SFDC_Scorecard Range 1 (Last Modified)}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row))), 1)

@Genevieve P. that did the trick! Thanks so much :)

@Genevieve P. - wondering if you can help me solve for another max collect formula use:

I am tracking the latest date a customer was approved for use as a reference.

I have a Reference Request Tracker sheet that records 1 row for each opportunity, and may have up to 5 customers entered as reference options. (Those 5 customer names are entered into 5 separate columns (ie. Cust Ref #1, Cust Ref #2, etc.)

I want to pull the final approved on date for each of the Customers when the Final Request status for the entire row is "approved".

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), 1)

Where...

Reference Request tracker Range 2 = Final request approved on Date,

Reference #1 = column "Reference #1"

Reference Request Range 2 = Final Request Status

This formula is resulting in either #Date Expected or #Invalid Data Type.

Can you help me identify the issue? Thanks!

It looks like the syntax is correct, which is great!

What type of column are you putting the formula into? Since it's returning a Date, you'll want to write the formula into a Date type of column.

Let me know if that fixed the error! 🙂

@Genevieve P. Unfortunately, it was already a date type column, so that didn't resolve.

My apologies, I missed that you had a 1 at the end of your formula:

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), 1)

This means that your formula is looking for the MAX between either a Date or a 1, which will give you an error. Either you'll need to replace the 1 with a Date value, or remove it all together:

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), DATE(2022, 01, 01))

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"))

Cheers,

Genevieve

@Genevieve P. That did the trick!

So, this solves for any customer approved for use as a reference #1. I need to apply the same logic for reference #2-5, and then have a summary of all 5 references to show the latest approved date for that one particular customer regardless of which reference # they were used as. Do you have a suggestion on best practice to accomplish this? I was assuming with helper cells (which is essentially what the formula you've helped me with here was starting) for each reference field and then a max collect across those values for each customer.

Thanks for all the help, you are incredible!!