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,

Find more posts tagged with

Formulas

functionality

Automations

Accepted answers

All comments

kowal

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.

Chris L

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

Kelly Moore

@Chris L

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.

https://help.smartsheet.com/topics/reports

Kelly

Kelly Moore

@Chris L

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.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.