+ Reply to Thread
Results 1 to 4 of 4

Sum Amount of Each Year Based on Range of Dates

  1. #1
    Registered User
    Join Date
    09-16-2012
    Location
    Sydney
    MS-Off Ver
    Office 2003
    Posts
    47

    Sum Amount of Each Year Based on Range of Dates

    I have two worksheets. I'd like one to contain the [/B]total amount I've spent each year for a particular year[/B] and the second contains what I've spent the amount on, the date purchased and the actual amount spent.

    I would like Worksheet 'Yrs' and Row 4 underneath each year to total the amount I've spent for that particular year. The data for this is in a 'Purchases' Worksheet and is sourced from dates with a format of 01/01/12 as an example.

    In the attached workbook (Sheet Purchases) for 2012 as an example, I would like the three 'Cost' totals for the 2012 dates to be added and then displayed under the 2012 heading in the 'Yrs' sheet. Then so on for each year

    I think this can be achieved by SUMPRODUCT and YEAR but I'm not sure.

    I'd be grateful for any help.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Sum Amount of Each Year Based on Range of Dates

    In B4 use..

    =SUMPRODUCT(--(YEAR(Purchases!$C$7:$C$13)=B3),Purchases!$B$7:$B$13)

    Copy across
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Sum Amount of Each Year Based on Range of Dates

    hi jaclrsen. try:
    =SUMPRODUCT((YEAR(Purchases!$C$7:$C$13)=B$3)*(Purchases!$B$7:$B$13))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    09-16-2012
    Location
    Sydney
    MS-Off Ver
    Office 2003
    Posts
    47

    Re: Sum Amount of Each Year Based on Range of Dates

    Hi,

    Thank you so much for your responses.

    I tried both of the solutions presented and they both worked!

    Thank you so much. I'm so appreciative!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1