# location and date count

✭✭✭✭✭

Alrighty

Need some help with a formula that will search a location and division for specifics and then look at the newest date from that location and division and give me a count since that date. the first two columns are static but the data sheet is updated by forms weekly. this is a shot of the metric sheet for the dashboard. i need it to find the most recent recordable date and give me a count on how many days it has been.

• ✭✭✭✭✭✭

Ok. You are going to want to start with a MAX/COLLECT to pull the most recent date for your location/division combo.

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Once we subtract that from TODAY(), we will know how many days it has been since the last submission for that particular location/division.

=TODAY() - MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

• ✭✭✭✭✭✭

Are you able to provide a screenshot of at least the column names on the source sheet as well?

• ✭✭✭✭✭
• ✭✭✭✭✭✭

Are forms populated at the top or the bottom of the sheet?

• ✭✭✭✭✭

bottom of the sheet

• ✭✭✭✭✭✭

Ok. You are going to want to start with a MAX/COLLECT to pull the most recent date for your location/division combo.

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Once we subtract that from TODAY(), we will know how many days it has been since the last submission for that particular location/division.

=TODAY() - MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!