Need help assimilating data based on row conditions

Stefan K.

Hi there,

I need some help regarding formulas in Smartsheet. I have a raw data entry sheet, where I am entering the number of Work orders my production team is working for the day. I am trying to find a way to pull in corresponding columns of data sets based on the date column. In the first photo, you can see the raw data entry my team inputs. I am trying to get the most current day item to always show up in today on my 2nd page, then aggregate the last 7 days in the column off to the right. I want that to update so everday it pulls in the new days value, and adjusts the last 7 days accordingly. I cant seem to get the sumif functions based on date to work correctly, or even get a single value to pull based on date. I know this ones simple but its not clicking. Thanks all!

Should I be doing this aggregate on a summary sheet? They are both worksheets.

KPH

To display the data from the plan column for today the SUMIF would look something like this

=SUMIF({Cross sheet reference to date column}, TODAY(), {cross sheet reference to the column you want to sum})

So for 801 plan the references might be

=SUMIF({Date}, TODAY(), {801 Plan})

Then for 801 action it might be

=SUMIF({Date}, TODAY(), {801 Actual})

This will depend on how you set up your cross sheet references. Are you familiar with those?

Can you get this to work?

KPH

To display the data from the plan column for today the SUMIF would look something like this

=SUMIF({Cross sheet reference to date column}, TODAY(), {cross sheet reference to the column you want to sum})

So for 801 plan the references might be

=SUMIF({Date}, TODAY(), {801 Plan})

Then for 801 action it might be

=SUMIF({Date}, TODAY(), {801 Actual})

This will depend on how you set up your cross sheet references. Are you familiar with those?

Can you get this to work?

Stefan K.

@KPH Thank you for the feedback! This did work well, which leads me to my next extrapolation. Now that I have figured out how to pull/sum a corresponding figure to a date, I would like to have the same function, but for a 7 day period (week to week) rather than just for the day. Rather than rolling over every day and always doing the last 7 days, I would like to have fixed 7 days time periods. How can I get smartsheets to return a sum of values for a 7 day period, rather than a single date? Do I have to sum them all as individual daily formulas?

If I wanted to do it as a rolling 7 days, how could I apply that condition?

Thanks!

KPH

You could use less/greater than or equal to today rather than equal to today.

For example, this would return the sum of 801 plan if the date is today or before today (i.e. less than or equal to today)

=SUMIF({Date}, <=TODAY(), {801 Plan})

This would return the sum of 801 plan if the date is on or after the date 7 days before today (i.e. greater than or equal to today minus 7) and should probably do what you need (assuming you don't have to exclude future dates).

=SUMIF({Date}, >=TODAY(-7), {801 Plan})

If you ever need to use more than one range, ie after a certain date but before another, then you can use a SUMIFS instead of SUMIF.