Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Column based on dates in another column (E

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •