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

Genevieve P.

Hi @Steve Grinder

What I would do in this instance is have a helper, hidden Date type of column in the sheet. Then I would use a Record a Date workflow to add in the date when the % complete cell changes to "100%".

That way you can reference this Date column in your MAX function to find the Max date (most recent date) and return the Task Name.

=INDEX(COLLECT({Task Name Column}, {Percent Complete Column}, 1, {Helper Date Column}, @cell = MAX({Helper Date Column})), 1)

I will note that if two tasks are completed on the same day, it will pull the first one in the list. You could always use JOIN(COLLECT if you wanted to see potential duplicates:

=JOIN(COLLECT({Task Name Column}, {Percent Complete Column}, 1, {Helper Date Column}, @cell = MAX({Helper Date Column})), ", ")

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Steve Grinder

Unfortunately - my options are limited to just the Report, as the sheet is Owned by a different Dept and I only have View Only rights

Genevieve P.

Hi @Steve Grinder

In a Report, you could use a Filter to only bring in rows that are 100% complete, then Sort the Report by the Date column so the most recent ones are at the top.

I will note that you can reference view-only sheets in cross-sheet formulas, so you could still bring back the most recent task with 100% using my formulas above, as long as the sheet owner has a date column you can reference for when that 100% was selected. Let me know if that makes sense!

Cheers,

Genevieve

dslifer

I'm trying to do something similar in a Project Sheet Summary, where I have a Status, Phase and End Date. What I'm trying to return is the Phase for the row with the latest completed status. I tried the formula above and keep getting 'Unparseable'. This is what I have:

=INDEX(COLLECT(Phase:Phase, Status:Status, Status:Status = "Complete", [End Date]:[End Date], @cell = MAX([End Date]:[End Date])),,)

What am I missing?

Genevieve P.

Hey @dslifer

A few minor adjustments to make with this formula.

1 - Think of the Collect function like a filter. The first range is the one to filter, then you list Range, Criteria, Range, Criteria and so on.

This means your Status filter should have the criteria "Complete" without repeating the range.

=INDEX(COLLECT(Phase:Phase, Status:Status, "Complete", ...

If you feel more comfortable with it, you could use @cell = "Complete" since you're saying "the cell equals complete":

=INDEX(COLLECT(Phase:Phase, Status:Status, @cell ="Complete", ...

2 - The Collect is a Filter for the column you're looking at. The INDEX tells you which of the rows that have been filtered to bring back. If you're using the filter to narrow down your results to only have 1 match, then you just need a 1 at the end of the formula:

=INDEX(COLLECT(Phase:Phase, Status:Status, "Complete", [End Date]:[End Date], MAX([End Date]:[End Date])), 1)

Keep in mind this will only work if your Max End Date for the entire sheet is always going to be for a row that's Complete. If you could have dates that are not complete that have a date in the future, you'll need to filter that criteria as well:

=INDEX(COLLECT(Phase:Phase, Status:Status, "Complete", [End Date]:[End Date], MAX(COLLECT([End Date]:[End Date], Status:Status, "Complete"))), 1)

Cheers,

Genevieve

dslifer

Genevieve, that worked like a charm! (although now I realize I have to put an 'IFERROR' condition around it). Thank you so much - especially for the explanation, as I plan to use this for additional summary values.

Thanks again!