What is the dynamic date formula to update based off a week worth of data?

Chris Edwards

I'm creating a dashboard for my department using a calculation sheet that pulls data from another sheet. While I've managed to come up with the basic calculations I need, I'm not sure how to make them dynamic so the dashboard displays data for the week (Sunday - Saturday). I don't want to have to manually change things from week to week if possible. Mind you, I'm not that great with formulas.

I'm using the following formula for once of my totals which refers back to a data sheet. In that same sheet, I have a created date column. What should the formula be?

=COUNTIF({Gate Check-in Coke Range}, 1)

Chris Edwards

I created a week # column that calculates the number of the week. Can I use that column in my formula somehow.

Nick Korna

Hi @Chris Edwards,

Yes, you can. You would amend your formula to COUNTIFS. If you were looking to get the data for the previous week (as an example):

=COUNTIFS({Gate Check-in Coke Range},1,{Week Number},WEEKNUMBER(TODAY(-1)-1))

You can use a similar function to mean you could skip the Week helper column, but for some reason I've only been able to get it to work properly with SUMIFS, not COUNTIFS, so whether this would be any use to you would depend on whether you have any numerical value to SUM or not (though you could obviously have a helper column to do this!).

Hope this helps, but if you've any problems/questions then just post! 🙂

Chris Edwards

That worked! Thank you so much.

What if I want to create another metric that would calculate sum based on the day of the week using one of the two columns above (week# or created)?

Then I can have the sum for each day of the week that will update my dashboard.

Nick Korna

Yes, you can use both. For example, for today (Tuesday):

=SUMIFS({Column to sum},{Created},WEEKDAY{@cell}=3,{Week Number},@cell=WEEKNUMBER(TODAY(-1)))

To change the day, alter the number after WEEKDAY (Sunday =1, Saturday = 7).

Chris Edwards

Nick,

What is @cell?

Nick Korna

When you are using a column reference, you use it to check a cell's contents for the specified criteria.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Looking at the above formula, I've messed up the brackets around the WEEKDAY portion of the formula - these should be rounded ( ), not { }.