# Grouping data in Excel 2007

I have one column the many, many dates (mm/dd/yyyy) in MS Excel.

in another column I have the total sales from that day.

ex. column A

\$50

\$75

\$100

Column B

09/10/11

08/08/08

08/09/11

How can I have the total sales for each month?

-

This will be a function of the Subtotal tool, which should be in the Data ribbon.

You'll probably want to create a third column just with the month of the date, I'm sure there's a way to do this programmatically, but I'm not sure how. The important thing is that in it's default use, Subtotals will have to run against every change in a column just with the month.

You may or may not want to sort the data by date first, then select all the data you wish to work with, including the column titles, and click Subtotal.

At each change in: (month) Use function: Sum Add subtotal to: (sale amounts)

This will add rows between the months with the total sales for that month as well as a final row with the grand total.

-
Thanks, I'll try it out – wizlog Oct 6 '11 at 22:59
+1 To create the third column with the month, you can use either MONTH(B1) or TEXT(B1,"mmm"). Or if you want to take the year into account, TEXT(B1,"mmm yyyy"). – Kaze Oct 6 '11 at 23:29
@Kaze thanks for that. Wizlog, by entering one of the formulae Kaze suggest in an empty column, selecting that cell, click the tiny square in the lower right corner of that cell highlight frame, and dragging down to the extent of the cells, Excel should auto-fill the remaining cells in that column with the appropriate formula adjusted for the row number. – music2myear Oct 7 '11 at 14:02