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

[chrism2029]

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>

 

[RJPotts]

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

 

[konew1]

Have you tried using a pivot chart?