# Formula for adding a weeks worth of data and compare previous week's data

#### chrism2029

##### New Member
What I am trying to do is put together a forumla that will show by day for a weeks worth of information. The data is being pulled from a Master List called "Sheet 1". The Master List has all kinds of other data on it that I will be used for other formulas. I just need the formula to extract the data from the master list into another Worksheet so I can put together a Bar Graph and a Line Graph (previous week) so I can compare week to week.

Some formulas I have used is the COUNTIFS along with 'Sheet1'!A:A,">=1/1/13",'Sheet1'!A:A,"<=1/7/13" to provide a range for either date or times.

I want to this to be either automatic or I can create a whole work sheet with the weeks already labled but want it to pull it from my Master List. I have used the COUNTIFS due to certain items I only need to relfect the data into a graph.

Hopefully someone can be able to answer this one for me since I am stumped on how I can possibly go about accomplishing what I want to do.

Master List "Sheet 1"
 Date Time Day Location Product Type Sales Refund 4/29/2013 1000 Monday San Fran Oranges 1 1 4/30/2013 1100 tuesday NY Nuts 1 5/1/2013 1500 wednesday San Fran Peaches 1 1 5/2/2013 1700 thursday NY Nuts 1 5/3/2013 1500 friday Florida Oranges 1 5/4/2013 1600 saturday Florida Oranges 1 1 5/5/2013 1000 sunday San Fran Peaches 1 1 5/6/2013 1400 Monday Florida Oranges 1 1 5/7/2013 1400 Tuesday San Fran Peaches 1 1 5/8/2013 1100 Wednesday Florida Oranges 1 5/9/2013 1600 Thursday San Fran Peaches 1 5/10/2013 1700 Friday Florida Oranges 1 1 5/11/2013 1000 Saturday San Fran Peaches 1

<tbody>
</tbody>

One Sheet to be filled with Sales. Just need the numbers in this one.
 Week Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 May 6-13

<tbody>
</tbody>

2nd Sheet to be filled with times by counting the times
 Week Times Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 10-1100 Apr 29- May 5 12-1500 Apr 29- May 5 16-1700 May 6-13 10-1100 May 6-13 12-1500 May 6-13 16-1700

<tbody>
</tbody>

3rd Sheet to be filled Locations
 Week Locations Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 San Fran Apr 29- May 5 Florida Apr 29- May 5 NY May 6-13 San Fran May 6-13 Florida May 6-13 NY

<tbody>
</tbody>

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
One Sheet to be filled with Sales. Just need the numbers in this one.
 Week Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 May 6-13

<tbody>
</tbody>

Assuming the above is top left of a sheet then change 'Week' to 'Week Commencing' and put a single date in there. On the same row, under Monday, enter the formula =SUMIF(Sheet1!\$A\$2:\$A\$14,Sheet2!\$A2 + 0,Sheet1!\$F\$2:\$F\$14), under Tuesday enter =SUMIF(Sheet1!\$A\$2:\$A\$14,Sheet2!\$A2 + 1,Sheet1!\$F\$2:\$F\$14), copy across the other days, each time changing the bold figure.

2nd Sheet to be filled with times by counting the times
 Week Times Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 10-1100 Apr 29- May 5 12-1500 Apr 29- May 5 16-1700 May 6-13 10-1100 May 6-13 12-1500 May 6-13 16-1700

<tbody>
</tbody>

I don't think you've provided enough information there to clarify what you're trying to do. Your example data has single times but the table immediately above has time ranges. Is it supposed to be a count of sales within those time-frames?

3rd Sheet to be filled Locations
 Week Locations Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 29- May 5 San Fran Apr 29- May 5 Florida Apr 29- May 5 NY May 6-13 San Fran May 6-13 Florida May 6-13 NY

<tbody>
</tbody>

I'm unsure what you're wanting there too. Is it just a list of which location appears against which day? If so, can there be more than 1 location per day?

Best regards
Richard

Have you tried using a pivot chart?

Replies
4
Views
843
Replies
5
Views
196
Replies
5
Views
176
Replies
3
Views
269
Replies
0
Views
212

1,219,808
Messages
6,150,353
Members
450,954
Latest member
Vd1408

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back