Group and Sum

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

0
anonymous (74721)
9/27/2004 1:57:27 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
642 Views

Similar Articles

[PageSpeed] 44

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


-- 
duane
------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11624
View this thread: http://www.excelforum.com/showthread.php?threadid=263701

0
9/27/2004 2:07:57 AM
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).

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Eileen" <anonymous@discussions.microsoft.com> wrote in message
news:195701c4a435$56f75c10$a501280a@phx.gbl...
> 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
>


0
nothanks4548 (968)
9/27/2004 2:10:52 AM
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.

On Sun, 26 Sep 2004 22:10:52 -0400, "Earl Kiosterud" <nothanks@nospam.com>
wrote:

>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).

0
anonymous (74721)
9/27/2004 2:29:38 AM
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

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=26370

0
9/27/2004 2:39:09 AM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:9kuel09cevn44q7qt9q969h9r9qkqaqqfe@4ax.com...
> 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.
>
> On Sun, 26 Sep 2004 22:10:52 -0400, "Earl Kiosterud" <nothanks@nospam.com>
> wrote:
>
> >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).
>


0
nothanks4548 (968)
9/27/2004 3:40:40 AM
Yes, "unpivot" it. I think there's info on John Walkenbach's site for a method
to do that.

>Or she could make a row that digs out the month from the headings, then use a
>pivot table for month grouping and totals.

0
anonymous (74721)
9/27/2004 4:12:31 AM
A link to John's Reverse Pivot technique:

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

Myrna Larson wrote:
> 
> Yes, "unpivot" it. I think there's info on John Walkenbach's site for a method
> to do that.
> 
> >Or she could make a row that digs out the month from the headings, then use a
> >pivot table for month grouping and totals.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/27/2004 9:40:26 PM
Reply:

Similar Artilces: