+ Reply to Thread
Results 1 to 17 of 17

Need formula to calculate monthly costs based on date range

  1. #1
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Need formula to calculate monthly costs based on date range

    Monthly view of costs.xlsx
    Attached is a file that contains 2 worksheets: Data and Monthly

    The Data sheet contains data that shows the total amount for a data range per Batch and Type.
    The Monthly sheet lists each unique entry of Batch and Type. I need a formula to enter into cells (C2-CH10) that will calculate those total costs on the Data sheet into the monthly amount and discplay that amount into those cells. I've hard entered the amounts the formula should return.

    Thanks for any assistance.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    See attached based on Dates in column C: If I use column D I get zeros .... which I don't understand.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    ...... solved ... bad day at the office ....!!!

    =SUMIFS(Data!$F$2:$F$66,Data!$A$2:$A$66,Monthly!$A2,Data!$B$2:$B$66,Monthly!$B2,Data!$D$2:$D$66,">=" &Monthly!C$1,Data!$D$2:$D$66,"<=" &EOMONTH(Monthly!C$1,0))

  4. #4
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    Hi John,

    Thank you for your attempt at this. Unfortunately when I copy that formula into cell C2 on the Monthly worksheet and drag down and across, I don't get quite the results I'm expecting. For example, Batch 2 HW: the formula calculates $7,367.22 in Dec-10 and nothing else until Dec-11. But it should have calculated $4,148.55 for Dec-10 all the way through Nov-14.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    I confess to not understanding the calculation required! how do you get $4,148.55 for Dec-10 to Nov-14? I am obviously "brain dead" today (and yesterday!).

  6. #6
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    Monthly view of costs.xlsx
    In this file you will see months going across with costs in them on the Data worksheet. This is only there to show how each monthly costs is calculated. These fields will not be there in the final file.

    The Monthly worksheet has the corrected dollar amounts as it apears in my original file, I had some calculations in the incorrect months. I apologize for the confusion on this.

    Thanks.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    Still struggling I'm afraid. I need to consolidate in "helper" table (Batch, Type, Start/End dates) and then work out how to allocate.

    I'll look again tomorrow as brained has died again!

  8. #8
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    The months going across in the Data worksheet show how the monthly amounts are calculated - it's the Total Cost spread over the number of months as shown in the Duration column and beginning in the month year in the Start column.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    Please take a look at the attached.

    I have added "helper" columns in "Data" to try to accumulate data over the required periods: the start and end columns designate the months (columns) for the data to be inserted (1= Jan 2010). I also added a column in "Month" for a search key used by the macro.

    In the absence of any other solution I used a simple macro to assign the values to the months.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    Can you tell me where I can find the formula in the Monthly worksheet that will show the monthly totals per Batch and Type?
    Thanks.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    There is no formula in the Monthly work sheet.

    The calculation of monthly totals is done in the "helper" columns in "DATA". There is SUMIFS calculation in Column J which sums by Batch/Type/Start Date/End Date.

    There are repetitive answers which the macro ignores when it summates for a particular month.

    I cloud not think of another way of doing it.

  12. #12
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    John, thank you for all your work. Unfortunately I could not use the file you sent as this was just a sample of a much larger file. But you gave me an idea on how write the formula I needed. This issue is now solved thanks to your ideas.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    For my own education I would be interested in your solution as I often "can't see the wood for the trees"!

    Thanks for feedback: sorry I could not provide a better solution.

  14. #14
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    My solution: in the Monthly worksheet in cell C2 I put the following formula and dragged across and down:
    =SUMPRODUCT((Data!$C$2:$C$66<=C$1)*(Data!$D$2:$D$66>=C$1)*(Data!$A$2:$A$66=$A2)*(Data!$B$2:$B$66=$B2),Data!$G$2:$G$66)

  15. #15
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    I forgot to mention that in the Data worksheet I added Monthly Totals in column G of the Data worksheet.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Need formula to calculate monthly costs based on date range

    Thank you.

    I haven't looked at the spreadsheet and applied your formula but it was the "missing" monthly totals which "prevented" me from using SUMPRODUCT in my earlier attempts.

    So I'll claim a little merit for the solution!

  17. #17
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Need formula to calculate monthly costs based on date range

    Yes, it was the Monthly Totals column that I needed and hadn't come up with without your assistance.

+ 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. Formula to calculate # of hours based on date range and employee ID code
    By r0man3 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-02-2014, 08:04 PM
  2. [SOLVED] Need help writing formula to calculate monthly returns based on annual growth rate
    By bxk006 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 12:07 PM
  3. Need formula to calculate unemployment insurance costs.
    By twylaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 03:53 PM
  4. Replies: 3
    Last Post: 03-12-2013, 12:14 PM
  5. Replies: 2
    Last Post: 07-06-2012, 02:22 AM
  6. Designing a formula to calculate western Union fees and shipping costs
    By MASTERforge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2007, 03:29 PM
  7. How to calculate shipping costs based on subtotal
    By mywaters in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2006, 10:31 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