Formulas to (a) pull month, year and (b) sum within date range?

[Ed from AZ]

I have a list of dates in column A, and numbers down cols B-E. Off to
the right, I need a table that lists the month/year and the totals for
each of the columns. It needs to be a separate table, rather than a
pivot table, so it can be copied-and-pasted by others.

What I _think_ I need is:
-- a formula in H2:H10 (maybe more) that will look down the full range
of dates (call it A2:A100) and put one month/year per rowH3 = 03/09
H4 = 04/09
etc.
-- a formula in I2 that will sum all entries in B2:B100 with the month
and year of H2

I think I could get the second formula using SUMPRODUCT if I knew a
formula to get the month/date. (Then again, maybe I'm absolutely
wrong!)

Any help is appreciated.
Ed

 

[Dave Peterson]

One way (with a real date in H2):

=sumproduct(--(text(a2:a100,"yyyymm")=text(h2,"yyyymm")),
(b2:b100))

Another way to do this kind of thing is through a pivottable.

Add headers to your data if you don't have them, then you can select the range
(A1:B100??), data|pivottable

Follow the wizard until you get to the step with a Layout button on it.
Click that button.

Then drag the header for the date to the row field.
Drag the header for the amount(?) to the data field. If it says Count of,
double click on that "button" and choose Sum.

Then finish up the wizard.

You'll end up with a nice summary per date--but not month.

Right click on the Date header in the pivottable.
Choose Group and show detail.
Then choose Group.
And group by months and years.

And you'll have the nice summary report for all your months.

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