Collecting Dollar amount with two reference columns

A C

Hello everyone, I'm in need of some help with collecting a dollar amount from one sheet to the next. My issues are the following; I have to collect the amount after meeting specific criteria. Of those being the "name" or "ID" in one column and then within a specific "date range" in a different column.

For example: on sheet 1 I have three columns. Column 1 we'll say is ID. Column 2 is the date (could either be a date range or a date specifying the beginning of the work week). And column 3 is a dollar amount.

On sheet 1 in column 2 (the date column) I have each week's date for the year. The column is formatted for date.

When ever I try to collect the dollar amount from sheet 1 to sheet 2, I receive various error messages. I have tried many different combinations of index(collect, match, if(and... and included date(2023, 1, 1) or, today () or, today (-7) in the sequence... but nothing seems to be working.

Here is an example of just one formula that has been tried:

=IF({Date Range}, @cell >= TODAY(-7), AND({ID}, [Division]@row, COLLECT({Total Dollar Amount}, 0)))

Any and all help is greatly appreciated. Thank you

A C

If anyone was working on trying to help with this one, I have figured it out. The formula looks like this:

=INDEX(MAX(COLLECT({Total Dollar Amount}, {Date Range}, @cell >= TODAY(-14), {ID}, [Division]@row)), 1)

Andrée Starå

Hi @A C

I hope you're well and safe!

Excellent!

Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!