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

chrism2029

New Member
Joined
Mar 29, 2013
Messages
1
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
139
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top