Rolling Sumif based on weeknumber.
Hello,
I am trying to program a metic that sums the total based on the previous weeknumber. This should continue automatically populate each week.
I'm thinking it looks something like:
=SUMIFS({plug}, {cut week #}, <=WEEKNUMBER(), {cut week #}, >=WEEKNUMBER(-1))
Thanks so much for your help
Answers
-
Hey @Sarah_lee123
You have a range shown in your formula that indicates weeknumber so I will assume you created a helper column to track weeknumbers? Or does that conversion still need to take place? A screenshot of your data would be helpful. I'll work from your date field
=SUMIFS({plug}, {your date field on source sheet}, WEEKNUMBER(@cell)=WEEKNUMBER(TODAY())-1)
If weeknumber already exist as a column in your sheet the formula becomes:
=SUMIFS({plug}, {Weeknumber column on source sheet}, @cell=WEEKNUMBER(TODAY())-1)
Does either of these work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!