Group Dates by Week?
 

I have the below list of days, and numbers corresponding to each day. I am trying to group the dates into weeks, with a total number for each week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33

Hi,

Have a look at the attached for one way of doing what I think you need.

You didn't stipulate which version of Excel you're using so I've included a SUMPRODUCT approach that will work on any version of Excel and a SUMIFS version that will work in 2007 or later.

Let me know if you need any of it explaining further.

Group Dates by Week?
 

On Fri, 8 Feb 2013 16:43:58 +0000, AuditorLisa wrote:


I have the below list of days, and numbers corresponding to each day. I
am trying to group the dates into weeks, with a total number for each
week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33

You could use a pivot table.

Put headers in the row above your data (e.g. Dates | Counts)

Insert/Pivot table.

Drag dates to the row labels area
Drag Counts to the Values area.

Right click on some date and select "group"
Group by days and select '7' for the number of days.
Format to taste

--------------------
Row Labels Sum of Count
1/3/2012 - 1/9/2012 756
1/10/2012 - 1/16/2012 374
1/17/2012 - 1/18/2012 33
Grand Total 1163

I'm not sure why but it is not allowing me to "Group" when I right click a date in the pivot table. I had tried the approach before and ran into the same problem. I'm wondering if it is because the date is followed by "Count" since I previously subtotaled a larger sheet to extract this data? Would you have any suggestions as to how to make the grouping function work?

Thank you Spencer for your help! I'm afraid I'm not super experienced with SUMIF functions, but your approach seems good and I love a reason to learn something new! Thanks again for taking time to respond to my problem! :)

Group Dates by Week?
 

On Mon, 11 Feb 2013 14:50:38 +0000, AuditorLisa wrote:

I'm not sure why but it is not allowing me to "Group" when I right click
a date in the pivot table. I had tried the approach before and ran into
the same problem. I'm wondering if it is because the date is followed by
"Count" since I previously subtotaled a larger sheet to extract this
data? Would you have any suggestions as to how to make the grouping
function work?

In order to group a column of dates in a pivot table, all of the entries in that column must be dates. If there are any text entries in the column, or dates that happen to be text rather than dates, you will not be able to group them.

You will need to clean up your data; perhaps you could just remove the subtotals, or filter them out.