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
-
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!
-
@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!
Answers
-
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!
-
But then next year, won't that same entry pop-up in same weeknr.?
Continuous Improvement Facilitator in HVAC industry || Timezone CES
-
@MPath Yes. You would need to use one of the solutions here in the Community that outputs (for example) the Monday of the week of a date. You would drop this into a helper column on the sheet, and then you would adjust the above formula to look at the helper column and compare to "current Monday minus 7".
=[Date Column]@row + (2 - WEEKDAY([Date Column]@row))
=COUNTIFS([Helper Column]:[Helper Column], @cell = TODAY(-7)@row + (2 - WEEKDAY(TODAY(-7))))
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!
-
or:
Datum:Datum; AND(ISDATE(@cell); WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1; YEAR(@cell) = YEAR(TODAY())))
? Correct, seems to work for me, do nto have data of last year yet, but simulated it.
Continuous Improvement Facilitator in HVAC industry || Timezone CES
-
@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!
-
Arghh! Did not think of that! Guess I'll be changing 50+ formulas again ;) Appreciate it!
Continuous Improvement Facilitator in HVAC industry || Timezone CES
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!