Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Overachievers Alumni

    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.

    Tomasz Kowalski

    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

  • Community Champion

    @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.

    Site faviconReports | Smartsheet Learning Center

    Kelly

  • Community Champion

    @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.

    Site faviconUse column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions