Need a formula for counting something on a weekly basis.

From the screenshot below, I need a formula to count the incoming mail based on each week. I want to show the information on a dashboard on a weekly basis which will exclude the weekends. For example here, I have the data for last week of Jan, but when we enter into next week, first week of feb, I want the dashboard to show new numbers as we enter daily. Is this possible?




Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Aparanji Veldurthy

    You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.


    A SUMIF works like this:

    =SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])

    In your case, the criteria is as follows:

    WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())


    So, put together, try this:

    =SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])


    You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.

    Let me know if this works for you or if you have any additional questions!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Aparanji Veldurthy

    You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.


    A SUMIF works like this:

    =SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])

    In your case, the criteria is as follows:

    WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())


    So, put together, try this:

    =SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])


    You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.

    Let me know if this works for you or if you have any additional questions!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!