+ Reply to Thread
Results 1 to 4 of 4

Return total amount values based on multiple criteria

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Return total amount values based on multiple criteria

    Hi, can someone help me on what formula to use in cell C4:BB6 under MASTERFILE on my attached file. I want to modify it in such a way that the the formula is based on the date headers and the rows that shows "Total" on the sheets.

    I do not want to simply reference a cell since the row numbers may change from time to time, and so instead I want my formula to be able to reflect the total amount for each week ending based on the dates and the row that has a row header of "Totals".

    Can you also help me on what conditional format formula should I use in sheet FORECAST - CONSTR. What I want to happen is for those rows to be highlighted in blue fill if the vlookup does not return a value for start date. And to highlight in blue with red font those cells in which the vlookup return no value and thus the dates were instead base on the forecasted days of return.

    Appreciate you help! Thank you so much!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Return total amount values based on multiple criteria

    IT appears that you need data from 3 tabs: 1."Forecast-Constr" (which gives you 'Cash Inflows' and same dollar amount for 'Construction' 2. 'Cash Outflow' 3. 'Pre Construction'. If you could summarize those 3 tables weekly totals(Macro?) and create a pivot table based on the Date, you would have all the data. Pivot table:

    1. ForecastConstr table summarized fields: Date; Total & 'ForecastConstr.
    2. 'Cash Outflow' table summarized fields: Date; Total & 'CashOutflow'.
    3. 'Pre Construction' table summarized fields: Date; Total & 'Pre Construction'
    I Transposed the data (see below each section's data)
    The Pivot table can be manipulated into your format to include the duplicated value for Construction.
    (it really looks more like a job for a database like access, but you can use Excel)


    good luck Mon

  3. #3
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: Return total amount values based on multiple criteria

    Hi queuesef,

    Uhm, actually I just want my Masterfile to be auto populated with the total amounts for each week from the corresponding sheets. My current formula is just referencing the totals which is somehow okay for me. But instead of me referencing the start cell in column C and dragging it across the sheet, I want it to instead be able to capture the total amount already with me not having had to do anything. Thus the formula in my Mastefile should be able to detect the row "Totals" and return the total amount for that week for the sheet. Preconstruction in Masterfile should reflect the totals for each week in the sheet FORECAST - PRE CONSTR. Construction is for FORECAST - CONSTR. and Cash Outflows is for CASH OUTFLOW.

    I hope I made it clear. Thank you so much!

  4. #4
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Return total amount values based on multiple criteria

    You can also use a combination of INDEX & Match to lookup the total. Or you could write VBA code to get last value from the Date you are looking up.

+ 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] Return amount values based on date and name criteria
    By MyStix01 in forum Excel General
    Replies: 2
    Last Post: 10-09-2017, 09:26 PM
  2. Replies: 3
    Last Post: 08-29-2017, 06:37 PM
  3. [SOLVED] Return Values based upon multiple criteria
    By frustrated in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2016, 08:56 AM
  4. [SOLVED] Return the total pay based on multiple criteria
    By jbeets in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2015, 01:47 PM
  5. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  6. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  7. Multiple criteria sum to calculate total amount per annum"
    By chunlee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2010, 08:30 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