Calculate total by week

**aharvestofhealth** · 11-11-2010, 12:26 PM

I am trying to calculate total sales by week.The example I am showing below is only a few days long. The actual report has several months. So for example I would like to show the total of sales by week in October - The total for days 4-10, 11-17, 18-24, 25-31. The intended results for the first week (days 4-10) are shown under the results below.

**arthurbr** · 11-11-2010, 04:10 PM

Hi, have you tried a Pivot table grouping date fields by week?

( it would be easier for all to add a sample sheet instead of an image, most of the members won't waste time retyping data)

**aharvestofhealth** · 11-12-2010, 11:23 AM

Ok, I changed it to a pivot chart, but I don't know how to change the date to show by week instead of by day. I've included a sample spreadsheet attachment below.

**arthurbr** · 11-12-2010, 11:48 AM

I think this is what you are looking for

You should first get rid of the blank cells. Depending on what your project requires ( is ti a one-shot, sill the list get longer, etc...) this can be done by several techniques.

When you have created the PT, right click, select Group - Group by days and select the number of days and the starting date. Then select any cell in the PT, press F11 to create a default chart

**scottylad2** · 11-12-2010, 12:06 PM

Has anyone pointed out your not really supposed to have threads going in 2 forums?

**aharvestofhealth** · 11-12-2010, 12:20 PM

Ok, I think that may work. How did you remove the spaces? Can I create a macro to do this?

@scotylad2 - My apologies.

**arthurbr** · 11-12-2010, 12:32 PM

If scotty was talking about cross-posting, please post the link here and read this first. Also post a link to the current thread there were you cross-posted

**aharvestofhealth** · 11-12-2010, 01:27 PM

I managed to delete all the empty spaces, but when right-clicking on the pivot chart, and selecting group, it only adds an additional field named group1. See my spreadsheet attached.