Collecting Dollar amount with two reference columns

Options
✭✭

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

• ✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!