How to count newly added tasks and closed task for previous week.

Hello,

Can someone help me to create formula for my requirement.

Everyweek starting on Monday, I need to look for how many tasks were added and how many were closed in previous week.

I attached sample xls sheet. As we know everyweek some tasks will be opened and some tasks will closed.

I would like to see a summary of the tasks were added in the previous week and closed tasks in the previous week.

Thanks,

Muni

Answers

  • Jaykel Torres
    Jaykel Torres Employee
    edited 05/13/21

    Hey @Muni Pavan Kumar Reddy Kona,

    By utilizing a combination of the COUNTIFSWEEKNUMBERTODAYNOT, and ISBLANK functions, we can evaluate if a task was newly created last week and if there were any closed tasks. I created the example below on how this may look like:

    New Tasks Last Week (Formula): =COUNTIFS([Date Added]:[Date Added], NOT(ISBLANK(@cell)), [Date Added]:[Date Added], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1)

    • This formula counts if the Date in our range is not blank and if the Date's Week Number is equal to Today's Week Number minus 1 (last week).

    Closed Tasks Last Week (Formula): =COUNTIFS([Date Added]:[Date Added], NOT(ISBLANK(@cell)), [Date Added]:[Date Added], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Status:Status, "Closed")

    • This formula counts if the Date in our range is not blank, if the Date's Week Number is equal to Today's Week Number minus 1 (last week) and the Status is "Closed".

    I hope this helps!

    Jaykel

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!