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)
-
@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".
Answers
-
Try something like this...
=COUNTIFS([Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)
-
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))))
-
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".
-
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
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
