Prev: Picture scroll with active cell
Next: determine how excel was opened
From: David Portwood on 29 Mar 2010 15:58
I have a small database consisting of dates and sales amounts. I would like
to subtotal the sales amounts for each month. However, no matter what I do,
Excel subtotals for each day. Is it possible to break this down by month? I
would appreciate any help. Thank you.

From: Dave Peterson on 29 Mar 2010 16:27
I'm not sure what you're using to subtotal, but if it's dates, then maybe you
can add a helper column to your data:

=text(a2,"yyyy-mm")
and drag down
(assumes that a real date is in A2)

David Portwood wrote:
>
> I have a small database consisting of dates and sales amounts. I would like
> to subtotal the sales amounts for each month. However, no matter what I do,
> Excel subtotals for each day. Is it possible to break this down by month? I
> would appreciate any help. Thank you.

--

Dave Peterson
From: David Portwood on 29 Mar 2010 16:52
Previously I was using a helper column, but that seemed awkward. I believe I
have found the solution: I changed the date format to "March-10" which I
found in Format Cells\Number\date and now subtotal works.

I am considering breaking it down by Year, then by Month within Year using
nested subtotals. For this I might need helper columns? Well, maybe I will
leave it as is.

Thanks for replying so quickly, Dave.

"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4BB10D34.25586874(a)verizonXSPAM.net...
> I'm not sure what you're using to subtotal, but if it's dates, then maybe
> you
> can add a helper column to your data:
>
> =text(a2,"yyyy-mm")
> and drag down
> (assumes that a real date is in A2)
>
> David Portwood wrote:
>>
>> I have a small database consisting of dates and sales amounts. I would
>> like
>> to subtotal the sales amounts for each month. However, no matter what I
>> do,
>> Excel subtotals for each day. Is it possible to break this down by month?
>> I
>> would appreciate any help. Thank you.
>
> --
>
> Dave Peterson

From: Dave Peterson on 29 Mar 2010 17:38
You may want to spend some time learning about pivottables. You'll be able to
group dates by month (and year) or by year.

If you've never used 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/assistance/2002/articles/xlconPT101.aspx

David Portwood wrote:
>
> Previously I was using a helper column, but that seemed awkward. I believe I
> have found the solution: I changed the date format to "March-10" which I
> found in Format Cells\Number\date and now subtotal works.
>
> I am considering breaking it down by Year, then by Month within Year using
> nested subtotals. For this I might need helper columns? Well, maybe I will
> leave it as is.
>
> Thanks for replying so quickly, Dave.
>
> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
> news:4BB10D34.25586874(a)verizonXSPAM.net...
> > I'm not sure what you're using to subtotal, but if it's dates, then maybe
> > you
> > can add a helper column to your data:
> >
> > =text(a2,"yyyy-mm")
> > and drag down
> > (assumes that a real date is in A2)
> >
> > David Portwood wrote:
> >>
> >> I have a small database consisting of dates and sales amounts. I would
> >> like
> >> to subtotal the sales amounts for each month. However, no matter what I
> >> do,
> >> Excel subtotals for each day. Is it possible to break this down by month?
> >> I
> >> would appreciate any help. Thank you.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
 | 
Pages: 1
Prev: Picture scroll with active cell
Next: determine how excel was opened