Formula to collect a $ amount with latest (max) submission date

Melisa Dannhauser

This seems so simple yet I cannot figure out a formula that works.

I need to collect a $ amount from a column based on multiple criteria, including a latest submission date. I played around with index, match, max, collect and I feel like I'm almost there but just can't figure it out. Here's what I have:

=INDEX(COLLECT(Budget:Budget,Status:Status,"Approved"),MATCH(MAX([Submission Date]:[Submission Date]),[Submission Date]:[Submission Date],0))

I keep getting "invalid value". The formula works fine at first but as soon as I add a new row of data on the bottom, the "invalid value" appears". I have tried so many variations but to no avail.

Basically, I want to collect the amount in "Budget" column where status column displays "Approved" but since I have multiple budget entries that are approved, I want only the number with the latest submission date. How can I do that and still be able to add data to my database without breaking that formula??

TIA!

Paul Newcome

Try getting rid of the MATCH function.

=INDEX(COLLECT(Budget:Budget, Status:Status, "Approved", [Submission Date]:[Submission Date], MAX([Submission Date]:[Submission Date])), 1)

Melisa Dannhauser

@Paul Newcome It still shows "invalid value" error. I had to change things around a little bit so here is my formula:

=INDEX(COLLECT(Budget1:Budget10, [Project ID]1:[Project ID]10, "project d", Description1:Description10, CONTAINS("Budget", @cell), [Submission Date]1:[Submission Date]10, MAX([Submission Date]1:[Submission Date]10)), 1)

I'm looking for budget for Project D where description includes budget and submission date is the latest date. The formula above displays "invalid value". I know why - the max date is looking at ALL dates when it should just be looking to pull the max date that matches the criteria for project d... I just don't know how to write a functioning formula for it. Ugh.

Paul Newcome

You can't nest a COLLECT inside of a COLLECT, so you will need to insert a date type column and use a MAX/COLLECT to pull the most recent date for that project.

=MAX(COLLECT([Submission Date]1:[Submission Date]10, [Project ID]1:[Project ID]10, "project d", Description1:Description10, CONTAINS("Budget", @cell))

Then reference this in the INDEX/COLLECT function:

=INDEX(COLLECT(Budget1:Budget10, [Submission Date]1:[Submission Date]10, [Max Date Column]@row, [Project ID]1:[Project ID]10, "project d", Description1:Description10, CONTAINS("Budget", @cell)), 1)

Melisa Dannhauser

Yup, I realize that now. I was hoping I can avoid the helper column, I have way too many. But I guess that's what I'll have to do. Thanks so much for your help!!

Paul Newcome

Happy to help. 👍️