+ Reply to Thread
Results 1 to 9 of 9

Formula For Sum of Spend Per Month During a Start and End Date

  1. #1
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Formula For Sum of Spend Per Month During a Start and End Date

    Hello All,

    Many Thanks for looking at this problem!

    Well, I have a database of spend per month using a sumif function. What I need is a formula that sums up the total spend between 2 dates and anything before or after these dates are not included in the total spend.

    So for instance, a person spends 100 pounds a month for 24 months between dates 01/01/11 - 01/01/13. But I need a formula to work out what the person spent between dates 04/09/11 - 04/07/12. This is needed for over 5000 lines of data.

    I hope iv explained that ok. Iv attached an example as well.

    Many Thanks

    Darren
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    here you go!
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    RCM
    Why is the concatenation needed in the formula below?

    =SUMIFS($E3:$AC3,$E$1:$AC$1,">="&$B3,$E$1:$AC$1,"<="&$C3)
    Click on star (*) below if this helps

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

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    @K m:
    that is needed in COUNTIF, SUMIF or AVERAGEIF when you are combining it with a cell reference. otherwise, it wont recognise it as a cell reference

    @Darren:
    i think your months in row 1 is always starting from 1st of the month. so is 04/09/11 - 04/07/12 excluding Sep-11 & Jul-12? if you were to include, are they supposed to be pro-rated?

    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

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,918

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    Sumifs

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  6. #6
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    Thanks so much guys!

    Benishiryo - Yes they are supposed to include those months.

    How would i change that formula to include only certain cells. For example iv attached another spreadsheet which is similar to the one above but iv added two coloums to each month. But I only want to include the invoice total for that month.

    On the attached spreadsheet, the amount under invoice total for each month is what i want to look at. That forumla by RCM is great but tis there a way to change to just accept them cells?

    I hope you understaned.

    Many Thanks

    Darren
    Attached Files Attached Files

  7. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,918

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    Hi
    You can use the SUMIFS.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    Thanks Kevin!! That Works perfectly!

    When I drag down that formula down for different data, the formula does not change. Am I doing something wrong?
    So in the above example, for person 2, person 3 etc.


    Cheers
    Darren

  9. #9
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula For Sum of Spend Per Month During a Start and End Date

    Cheers Guys! All Sorted.

    Thanks
    Darren

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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