Any way to update formula criteria with drag-fill
Hello,
I have a sheet with around 3000 rows all of which have a date received and I am trying to count how many were received per week.
Currently I have a helper column which has the week number (1-52) and have =COUNTIF([Week number]@Row, =1) which works but I am wondering how to then extend the formula to automatically change the week number so the next row would be =COUNTIF([Week number]@Row, =2) and so on as I do not fancy manually changing the number 52 times.
Is there any way to automate this?
Cheers,
Answers
-
hi @Chris L,
what about creating a function column with this function inside: =WEEKNUMBER(Created@row)
and the column "created" is the the system column date created that automatically sets the date when the new row was added with this solution you will automatically always have information which week each row was created.
Hope this helps.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time.
MASA Consult - Your Aligned Smartsheet Gold Partner
Find us on LinkedIn!
Tag my name: @kowal if you need quick response.
-
What I am trying to do is essentially make a table in a separate sheet that counts on week 1 there were x number of rows, week 2 there were y number
But I do not want to write 52 different COUNTIFs I want the number circled red to update automatically when I pull down to extend the formula
-
After implementing @kowal 's sage advice, consider using a report to then gather your totals. Information from a report can be displayed on a dashboard. Once the report is built (you will group and summarize the ROW report), the report will be automatically updated each week - completely hands-free and evergreen.
Here is information on reports, if needed. Feel free to shout out and I can help with any questions.
Kelly
-
If you already have a grid set up so that your 52 week numbers (some years have 53) are labelled in 52 rows, your COUNTIFS would be:
=COUNTIFS([Week number]:[Week Number], [Week number]@row)
Note that the 'r' in row is lower case. Also, you might consider adding a Year helper column since Week number repeat each year. If Year was added, your COUNTIFS would become:
=COUNTIFS([Week number]:[Week Number], [Week number]@row, Year:Year, Year@row=YEAR(TODAY()))
Depending how your sheet is constructed, you might be able to use a Column formula vs having to manually drag the formula down your sheet. For more info see the link below.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!