+ Reply to Thread
Results 1 to 12 of 12

Formula to Sum by Month and Financial Year

  1. #1
    Registered User
    Join Date
    06-28-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    5

    Formula to Sum by Month and Financial Year

    I have a spreadsheet that consists of date, product number with amount received against each date.

    What I need are formulas that will automatically provide me with:
    1. The sub-total monthly income for each product against each financial year.
    2. Total income for each product by financial year.
    3. Total income for each financial year.

    I have attached the spreadsheet with an example. Hope someone can help. Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,639

    Re: Formula to Sum by Month and Financial Year

    Which is your financial Year, Jan to Dec or April to Mar
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,639

    Re: Formula to Sum by Month and Financial Year

    see the attached file
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,950

    Re: Formula to Sum by Month and Financial Year

    Hi this is set up for the UK financial year. Easy to reset to Aussie FY if not 01/04/ to 31/03...
    Attached Files Attached Files
    Glenn



  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Formula to Sum by Month and Financial Year

    you can also use a pivot table to get the result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    06-28-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Formula to Sum by Month and Financial Year

    Hi Siva, thank you for the great example. Just one question, our financial year starts from 1 July and ends on the 30 June. I tried to work out your formula by making the following changes:

    SUMPRODUCT(($A$10:$A$20>=DATE(2000+LEFT($A3,2)*1,4,1))*($A$10:$A$20<=DATE(2000+LEFT($A3,2)*1+1,3,31))*(B$10:B$20))
    to
    SUMPRODUCT(($A$10:$A$20>=DATE(2000+LEFT($A3,2)*1,7,1))*($A$10:$A$20<=DATE(2000+LEFT($A3,2)*1+1,6,30))*(B$10:B$20))

    but I seem to be having a problem...
    kind regards Colin

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,639

    Re: Formula to Sum by Month and Financial Year

    Yes it is working, see the attached file
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,950

    Re: Formula to Sum by Month and Financial Year

    And my version set to your FY...

    Though, to be honest, I prefer nflsales's method to set the dates!!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-28-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Formula to Sum by Month and Financial Year

    Thank you Siva, you have given me the answer.

  10. #10
    Registered User
    Join Date
    06-28-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Formula to Sum by Month and Financial Year

    Hi Glenn, I do like the way you did the formula, as it enable me to see the date. I tried to modify the formula to calculate the total for each month by financial year, but my attempts did not work, for example Nov 2012, did not give me the correct answer:

    =SUMPRODUCT(--($A$9:$A$99>="01/11/2012"+0)*($A$9:$A$99<="30/11/2012"+0),B$9:E$99)

    Thanks for your input.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,950

    Re: Formula to Sum by Month and Financial Year

    I never even noticed that bit. Try this. There is a bit of manual intervention needed in Februarys of Leap Years, where you have to change the 28 in column P to a 29. I also rearranged the months to be in sequence for the Aussie FY.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-28-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    5

    Re: Formula to Sum by Month and Financial Year

    Quote Originally Posted by Glenn Kennedy View Post
    Try this. There is a bit of manual intervention needed in Februarys of Leap Years, where you have to change the 28 in column P to a 29. I also rearranged the months to be in sequence for the Aussie FY.
    Great, thanks heaps, it works perfectly...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Increment Financial/Fiscal Year date formula
    By ExcelSprinter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2013, 01:26 AM
  2. Need help with formula for find time (in months) for any given financial year
    By chandni_sharma in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-14-2013, 11:52 AM
  3. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM
  4. Replies: 4
    Last Post: 02-14-2010, 05:21 AM
  5. End of financial year from Today() formula
    By dwayneh in forum Excel General
    Replies: 1
    Last Post: 09-15-2006, 01:42 AM

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