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

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!

Find more posts tagged with

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

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.