Thread: Daily, weekly, monthly chart to put in graph

Hello,

I have checked other threads before posting here, but even though I found lots of information, nothing was truly adapted to my situation.

Here is my problem:

I extract from a huge data sheet thousands rows of sales informations.
Using a pivot chart, I regroup those in a simple chart.

Then, from the pivot chart, I regroup my products by line and show the sales per day.
The chart looks pretty much like this:

01/06 02/06 03/06 04/06 05/06
orange 10 12 13 14 9
banana 2 0 6 4 8
kiwi 6 11 12 5 10

Ok, so far, all's good. Exporting this to a graph is piece of cake.

Now, I want to regroup those data in weekly and monthly sales. I have tried many methods, even merged cells (don't use merge cells!! I knowwwww), but nothing works.
Here is the concept:


A1 B1 C1 D1 E1 F1
01/06 02/06 03/06 04/06 05/06 06/06
orange 10 12 13 14 9 0
banana 2 0 6 4 8 5
kiwi 6 11 12 5 10 8
week 1 week 2
orange 212 320
banana 32 60
kiwi 156 49
month 1
orange 652
banana 452
kiwi 365

I have tried to organize it in a logical way: the week goes under each sunday, and the month under each first day of the month... logical, right?
Then I put all this in a graph... Problem is: for weeks, cells A1 and B1 are empty (in fact only one cell is full every 7 cells, to be really logical). Result: my graph shows one bar, six gaps, one bar, six gaps, etc...
Same for months (of course).

So, I tried merged cells... but as expected, even though "A1:C1" becomes just one cell... the graph still see several: problem not solved.

I have also tried to create a separate table: my weeks are not aligned with the days, but whatever, it should work, right?
No, it doesn't. Here is the table:

A50 B50 C50 C50
week1 week2 week3 week4
orange 510 112 413 614
banana 22 50 26 504
kiwi 206 111 12 125

Alright... making a graph out of it: easy.
Problem: getting those data in this chart!

How do I do. I say:

A50 = sum(A1:G1) means the sum of all the sales for this week.
Ok... then I do the same for B50:
B50 = sum(H1:O1) ok, still good.

Now, I drag my formula to have the same system everywhere... right. But it doesn't work.
Excel will copy this:

C50 = sum(I1:P1)
D50 = sum(J1:Q1)
etc...
where it should be:
C50 = sum(P1:V1)
D50 = sum(...)

Therefore, I do not get proper results, and, indeed, not proper graph.

If anyone had a solution, I would be very grateful.
If anything isn't clear, please let me know.

Thank you very much.

Edit: sorry my tables aren't clear, but I can't find out how to draw nice table on the website.
edit-edit: I added a picture in attachment for the table

If your column field is a date then you should be able to group by date.

If not you may need to add another field or 2 to you source data table.

A sample workbook would be more helpful if you need more help.

I thought you said you had a pivot table. That is just a bunch of cells.

In order to remove the gaps create a weekly summary table

In C9 use
=SUM(OFFSET($C4,0,(COLUMN(C4)-COLUMN($C$3))*7,1,7))

Drag across and down.