Count entries made the previous week

Hi,

I am looking to create a sheet summary formula that will count all entries on a sheet that came from the previous week. I figure that it will have to be a countif formula and will incorporate weeknumber but I am lost on how to count from the previous week and have it keep updating for the next week.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...

    =COUNTIFS([Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @MPath I'm not sure that will work when the current week is the first week.

    If

    WEEKNUMBER(TODAY()) = 1

    then

    WEEKNUMBER(TODAY()) - 1 = 0

    There is no week number zero. Not to mention during that first week, the year would also need to be -1 (for part of the week). Yearly transitions with WEEKNUMBER can be very tricky to navigate because you could have week number 52 that has two different years in it, week number 53 that has two different years in it, and potentially week number 1 that has two different years in it depending on what day of the week the first of the year falls on.

    Using my method above, we ignore all of that and just leverage Mondays. We output the Monday for each of the dates so that every row has a Monday date (of the same week as the original date). Then we count how many of those Mondays are "last week's Monday".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!