Add Column based on dates in another column (Excel 2003 sp2)

Topic

New Reply

Hello,

I have a simple task to perform that is beyond me at this point. I have the following data columns:

Date Miles

I want to add up the # of miles in each month of the year. I was thinking I could use sumif to add the miles column if the date was inside of a given month, but I can not figure out how to treat the date as a number instead of a date so I could use sumif. I also do not know how to put a double bound on the date (i.e., Between January 1st and January 31 – more simply if (month = January) )

Do I need to create a hidden column that extracts the month from the date, then use match inside the sumif function?

My solution right now is to extract the month using month(date column), and then summing based on that value, but that seems too brute force

Thanks,

Andy

Reply | Quote

Replies

You could create a pivot table, with the date in the row area and the miles in the data area (with Sum as aggregation function).
Then right-click the date field in the pivot table and select Outline and Details | Group | By Months…

Another possibility: let’s say the dates are in column A and the miles are in column B, starting in row 2.
You have created a list of dates representing the first of each month in column D, starting in row 2.
Enter the following formula in E2:

=SUMPRODUCT((YEAR($A$2:$A$100)=YEAR($D2))*(MONTH($A$2:$A$100)=MONTH($D2))*$B$2:$B$100)

and fill down as far as needed. See the attached very small sample workbook.

Reply | Quote

Thanks Hans!

You made my hack and whack solution way more elegant. Now if I can only remember this one this time

Thanks again!

Andy

Reply | Quote