Group and Sum

[Eileen]

I have last 52 weeks sales data. How can I quickly group
by month and sum up monthly total? Below are samples of my
data.

Product 8/1 8/8 8/15 8/22 8/29

ABC1 2499 0 0 0 250
ABC2 1006 726 670 56 223
ABC3 5615 6107 3482 2808 2639
ABC4 17822 28044 16031 9872 8365
ABC5 3888 972 972 778 778
ABC6 4685 5581 4644 4399 2118

 

[duane]

easy to do with sumproduct

basically

=sumproduct((week range>=month1)*(week range<month2)*(sales date
range))

where week range is cell range with your weekly dates

month1 is say 1/1/04 (Jan), month 2 is 2/1/04 (Feb), etc

this example would give you sales for Jan

use this formula for each month

 

[Earl Kiosterud]

Eileen,

Since you have each month in a separate column already, you need only put in
formulas that give you the sums. Put the cell pointer under the last item
of the first column (8/1). Click the Autosum button. Ensure that the range
(marching ants) goes from the topmost cell to the last -- drag through the
cells if necessary to change. Press Enter. That should give you the total
for that month. Now copy that formula to the other columns by dragging the
fill handle (lower right corner).

 

[Myrna Larson]

Hi, Earl:

Looked to me as though she has a column for each week rather than each month,
i.e. the equivalent of a pivot table grouped by week.

 

[Domenic]

Assuming that the first row contains your dates (true date values
starting at B1, and your data starts on the third row as your exampl
indicates...

On Sheet 2, list each month on the first row starting in A1 in th
following format...

January 2004.....February 2004.....March 2004.....etc.

A2, copied across:

=SUMPRODUCT((MONTH(Sheet1!$B$1:$F$1)=MONTH(A1))*(YEAR(Sheet1!$B$1:$F$1)=YEAR(A1))*(Sheet1!$B$3:$F$8))

Adjust the range for your dates to cover the full year (B1:...) and th
range for your data to suit your table (B3:...).

Hope this helps

 

[Earl Kiosterud]

Myrna,

Oh. Yeah, you're right. Well, I guess she wants the months grouped by the
week's figures, since that's all she has. Seems she could manually make
similar SUM formulas as I suggested, two-dimensional, for each month. Or
she could make a row that digs out the month from the headings, then use a
pivot table for month grouping and totals.

 

[Myrna Larson]

Yes, "unpivot" it. I think there's info on John Walkenbach's site for a method
to do that.

 

[Dave Peterson]

A link to John's Reverse Pivot technique:

http://j-walk.com/ss/excel/usertips/tip068.htm