Microsoft Excel tutorial on how to count records by week.
This video includes three methods:
Using Group Field in a pivot table to roll up to weeks.
Using a formula of Data minus WEEKNUM(,3) to back the date to Monday.
Using ISOWEEKNUM plus the year along with SORT, UNIQUE, and COUNTIF.
To download the data, visit: Excel Count By Week - Episode 2589 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Count by Week in Excel pivot table
(0:43) Pivot convert dates to weeks
(1:33) Using date minus WEEKDAY(,3)
(2:19) Sort, Unique, COUNTIF
(2:53) Using ISOWEEKNUM in Excel
(3:30) Excel formula for Year and Week Number
This video includes three methods:
Using Group Field in a pivot table to roll up to weeks.
Using a formula of Data minus WEEKNUM(,3) to back the date to Monday.
Using ISOWEEKNUM plus the year along with SORT, UNIQUE, and COUNTIF.
To download the data, visit: Excel Count By Week - Episode 2589 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Count by Week in Excel pivot table
(0:43) Pivot convert dates to weeks
(1:33) Using date minus WEEKDAY(,3)
(2:19) Sort, Unique, COUNTIF
(2:53) Using ISOWEEKNUM in Excel
(3:30) Excel formula for Year and Week Number
Transcript of the video:
I could think of three ways today.
But I was surprised, when the third way jumped up and bit me in the butt.
Maybe you have something better.
How to count by week in Excel.
Moe Sadr found this old question, how to count records by month. And he says, "Well, this is great, but how do you do it by week?" I have three different ways today.
The first method is a pivot table.
So we have these daily dates here.
I'm going to insert pivot table, put it in an existing worksheet right there.
Click okay.
I only have one field, so I take the date field, move it to rows.
I take the date field, move it to values.
And I get a count of each day.
Now we want to roll these up to weeks.
So we're going to go into group field.
It starts out with a default of months.
You need to unselect months and choose only days.
When I choose only days, this spin button here becomes enabled where I can say number of days and roll it up to seven-day periods.
Now, you need to be careful because it's going to start on the first day, which is a Saturday in this case.
And if you want your week to start on Monday, let's say, you're going to want to back that up from the Saturday to the previous Monday.
That would be 12/27 of the previous year.
Click okay.
And there we are.
We have seven-day periods with the count.
Another way to go is a great function called the WEEKDAY function.
I'm a big fan of using this, particularly with this one here.
The number is zero through six.
So that's argument three.
Tells me a code for the day of the week.
The way that this is really useful, skip column B, take the date minus the WEEKDAY comma 3, and when we double click to copy that down, it backs everything to the previous Monday.
When a date already started on Monday, the fact that we get a zero for the weekday, make sure that it stays on that Monday.
Once you have the week of date in column C, over here in column E, I use the =SORT of the UNIQUE of that range of week starting dates.
That gets me a sorted list of all of the items.
And then in the next column, a COUNTIF of all of this data, C4 to C892, looking at each of the items in E4#.
Of course, E4# is the nomenclature to say we want to run this COUNTIF for each item in the E4 array.
Now before I move on to method three, let's just note here that the answers for the first few weeks are four, eleven, and nine because we're going to run into some trouble with ISOWEEKNUM of the date.
This tells us which week of the year numbered one to 52, although every seventh years there's a one to 53.
When I do this SORT of the UNIQUE, notice that the counts are wrong.
They don't match the previous item.
And that's because my data here spans two years.
I have data both in 2022 and 2023.
So I had to change this formula to add in the year of (A4)&"-W" in quotes.
And then the text of ISOWEEKNUM using two digits.
That gets me something that shows me both the year and the week.
The SORT needs the 01, 02, 03 in order to sort this correctly.
So you have three methods.
Personally, I would go with the pivot table, but WEEKDAY or ISOWEEKNUM are alternatives.
Thanks to Moe for sending this question in.
Thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions in the comments down below.
But I was surprised, when the third way jumped up and bit me in the butt.
Maybe you have something better.
How to count by week in Excel.
Moe Sadr found this old question, how to count records by month. And he says, "Well, this is great, but how do you do it by week?" I have three different ways today.
The first method is a pivot table.
So we have these daily dates here.
I'm going to insert pivot table, put it in an existing worksheet right there.
Click okay.
I only have one field, so I take the date field, move it to rows.
I take the date field, move it to values.
And I get a count of each day.
Now we want to roll these up to weeks.
So we're going to go into group field.
It starts out with a default of months.
You need to unselect months and choose only days.
When I choose only days, this spin button here becomes enabled where I can say number of days and roll it up to seven-day periods.
Now, you need to be careful because it's going to start on the first day, which is a Saturday in this case.
And if you want your week to start on Monday, let's say, you're going to want to back that up from the Saturday to the previous Monday.
That would be 12/27 of the previous year.
Click okay.
And there we are.
We have seven-day periods with the count.
Another way to go is a great function called the WEEKDAY function.
I'm a big fan of using this, particularly with this one here.
The number is zero through six.
So that's argument three.
Tells me a code for the day of the week.
The way that this is really useful, skip column B, take the date minus the WEEKDAY comma 3, and when we double click to copy that down, it backs everything to the previous Monday.
When a date already started on Monday, the fact that we get a zero for the weekday, make sure that it stays on that Monday.
Once you have the week of date in column C, over here in column E, I use the =SORT of the UNIQUE of that range of week starting dates.
That gets me a sorted list of all of the items.
And then in the next column, a COUNTIF of all of this data, C4 to C892, looking at each of the items in E4#.
Of course, E4# is the nomenclature to say we want to run this COUNTIF for each item in the E4 array.
Now before I move on to method three, let's just note here that the answers for the first few weeks are four, eleven, and nine because we're going to run into some trouble with ISOWEEKNUM of the date.
This tells us which week of the year numbered one to 52, although every seventh years there's a one to 53.
When I do this SORT of the UNIQUE, notice that the counts are wrong.
They don't match the previous item.
And that's because my data here spans two years.
I have data both in 2022 and 2023.
So I had to change this formula to add in the year of (A4)&"-W" in quotes.
And then the text of ISOWEEKNUM using two digits.
That gets me something that shows me both the year and the week.
The SORT needs the 01, 02, 03 in order to sort this correctly.
So you have three methods.
Personally, I would go with the pivot table, but WEEKDAY or ISOWEEKNUM are alternatives.
Thanks to Moe for sending this question in.
Thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions in the comments down below.
