+ Reply to Thread
Results 1 to 6 of 6

sum material costs based on a date range

  1. #1
    Registered User
    Join Date
    10-15-2020
    Location
    Southampton, England
    MS-Off Ver
    Professional plus 2016
    Posts
    41

    sum material costs based on a date range

    Hi!

    Please can you help me with this cost calculator...

    I would like the month columns in table2 to represent the associated month.
    E.g. "+1mon" = "July" (Report date + 1month)
    "+2mon" = "August" (Report date + 2months)
    based on the month of the report date

    I would like table 1,column6 onwards to duplicate the month name above
    e.g. column6="July", column7="August" etc.

    In table2 I would like to

    show the summed "cost_total" columns from table4 in the associated month column of the materials row of table2
    E.g. H5 should show the total for July (£342),H6 for August (£80) etc.

    Thank you very much.

    J
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: sum material costs based on a date range

    1. You cannot have formulas in table headers. The only solution would be to add a new row below the header with the date formulas, then you could hide the row of table headers.

    2. Table4 has fields: ORDER_NO , VENDOR_NO, SUPPLIER & PART_NO which do not map to any fields in table2; so, I don't see any reason why COST_TOTAL/342.76 should go to table2 H5 just because the month table4,E2 is July.

    Attachment has added rows to calculate out months base on the report date (moved to A1)...
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    10-15-2020
    Location
    Southampton, England
    MS-Off Ver
    Professional plus 2016
    Posts
    41

    Re: sum material costs based on a date range

    Hi Ben,

    thanks for that.

    Re. the monthly costs I would like the materials row to be the sum of all costs from table 4 that fall within the month.

    I have put an example in for July but I am looking for a better way to do this.

    KR James
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: sum material costs based on a date range

    Perhaps the following will be a better way:
    1. Change the formula for cell H3 as follows and then drag the fill handle over to cell M3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Change the formula for cell H7 as follows and then drag the fill handle over to cell M7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    10-15-2020
    Location
    Southampton, England
    MS-Off Ver
    Professional plus 2016
    Posts
    41

    Re: sum material costs based on a date range

    Thank you JeteMc. That looks like it has sorted it. Really appreciate you support. KR, James

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: sum material costs based on a date range

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] Find out the unit price according to the date and material within the date range
    By rayhen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2022, 05:20 AM
  2. [SOLVED] Calculate Total Costs based on Recurring Costs, Start Date, and Frequency
    By edaniel202 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-05-2021, 12:18 PM
  3. Matching Material Costs to Production in Chronological Order
    By Jonny757 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-16-2018, 08:45 AM
  4. [SOLVED] Need formula to calculate monthly costs based on date range
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-22-2015, 01:13 PM
  5. Replies: 0
    Last Post: 01-28-2014, 07:37 AM
  6. Replies: 4
    Last Post: 06-02-2012, 11:26 AM
  7. Excel 2007 : Lookup of material price based on date range
    By posttoamit in forum Excel General
    Replies: 1
    Last Post: 02-18-2011, 08:58 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