[SOLVED] Sum values on monthly base

Add a single row of headers to your table (date and qty???).

Select the table (include the header row)
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the date header button to the row field
drag the qty header button to the data field
If it doesn't say "sum of", then double click on that button and choose Sum.

And finish up the wizard.

Now rightclick on the date button
choose group and show detail|Group|by months
(or months and years, probably???)


If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

"news.telenet.be" wrote:
>
> Although the suggestion off Bob works like a charm, which I thank him for.
>
> I am still interested how it could be done with pivot table.
>
> As a newbie, I had a look at it and it seems a nice feature.
>
> Yet I cannot find the right placement to view the correct result in a pivot
> table.
>
> Can someone give me some more explanation on how to do this or is a pivot
> table not useable with only my 2 column data.
>
> Thx in advance,
>
> >"news.telenet.be" news:zfQ1g.382811$HC6.11122760@phobos.telenet-ops.be...
> >
> > Hello,
> > Can someone help me with this problem:
> >
> > A B
> > 1/1/2006 152.3
> > 3/1/2006 156.2
> > 8/1/2006 186.1
> > 5/2/2006 185.2
> > 19/2/2006 145.2
> > .. ..
> > I need to sum from all data on a monthly base.
> > Jan : ..
> > Feb : ...
> > March : ...
> > I already tried with a new column C who has the month number and then
> > using
> > the "Sum. If" function. But can it be done without a extra column just 1
> > formula per month?
> >
> > Thx in advance.

--

Dave Peterson

It worked.

I found the problem thanks to your description and the links.

I didn't had the grouping ability because one cell had a bad formatting.



Thank you very much.


"Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
news:444CC292.26A44913@verizonXSPAM.net...
> Add a single row of headers to your table (date and qty???).
>
> Select the table (include the header row)
> Data|Pivottable
> follow the wizard until you get to the step with a Layout button.
> Click that button
> Drag the date header button to the row field
> drag the qty header button to the data field
> If it doesn't say "sum of", then double click on that button and choose
> Sum.
>
> And finish up the wizard.
>
> Now rightclick on the date button
> choose group and show detail|Group|by months
> (or months and years, probably???)
>
>
> If you want to read more about pivottables...
>
> Here are a few links:
>
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://peltiertech.com/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
>
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistan...lconPT101.aspx
>
> "news.telenet.be" wrote:
>>
>> Although the suggestion off Bob works like a charm, which I thank him
>> for.
>>
>> I am still interested how it could be done with pivot table.
>>
>> As a newbie, I had a look at it and it seems a nice feature.
>>
>> Yet I cannot find the right placement to view the correct result in a
>> pivot
>> table.
>>
>> Can someone give me some more explanation on how to do this or is a pivot
>> table not useable with only my 2 column data.
>>
>> Thx in advance,
>>
>> >"news.telenet.be"
>> >news:zfQ1g.382811$HC6.11122760@phobos.telenet-ops.be...
>> >
>> > Hello,
>> > Can someone help me with this problem:
>> >
>> > A B
>> > 1/1/2006 152.3
>> > 3/1/2006 156.2
>> > 8/1/2006 186.1
>> > 5/2/2006 185.2
>> > 19/2/2006 145.2
>> > .. ..
>> > I need to sum from all data on a monthly base.
>> > Jan : ..
>> > Feb : ...
>> > March : ...
>> > I already tried with a new column C who has the month number and then
>> > using
>> > the "Sum. If" function. But can it be done without a extra column just
>> > 1
>> > formula per month?
>> >
>> > Thx in advance.
>
> --
>
> Dave Peterson