Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks, I'll try it out –  wizlog Oct 6 '11 at 22:59
1  
+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"). –  Ellesa 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.