Collecting Dollar amount with two reference columns

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!