Help with Subtotal Function

[David Portwood]

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]

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]

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]

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