Results 1 to 10 of 10

Formula to Sum Specified Cells Decided by Multiple Dates and Titles

Threaded View

  1. #1
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    Formula to Sum Specified Cells Decided by Multiple Dates and Titles

    I have two separate formula's under the Project Year's section (Cells B8 and C8) that are incomplete and I cannot figure out the last part of the equation I need to make it fully work.

    What I do have working is based on the Reporting Month of Mar-19 (Cell: C2), it will scan across the lower Project Month's section for the month and year (Cells: B11:Y11), whether it's listed as a Forecast or Actual Cost (Cells: B12:Y12), and lastly sums up the correct dollar amounts below (Cells: B13:Y13).

    So as shown in the example above, the formula under the Project Year's section in 2019 has a total of $37,925 (Cell: B8). The current formula takes in account of the Reporting Month of Mar-19 (Cell: C2), then it will sum all the dollar amounts on that month (Mar-19) and past month's (so in total, Jan-19 thru. Mar-19) that only have the title of Actual Cost, then it will sum all the dollar amounts in the very next month (Apr-19) and all future month's (so in total, Apr-19 thru. Dec-19) that only have the title of Forecast. In the final total in the Project Year's, 2019 total is $37,925.

    The problem is that in the Project Month's section, the start date may not always begin with the first month of the year. So, with my current fixed formula that is only set between the 12 months of 2019, then another second formula set between the 12 months of 2020. If the starting date in the Project Month's section is set to begin on, let’s say Feb-19 (see dropdown dates in cell C4 to change the example), then my fixed formula of 12 months starts on Feb-19 and unintentionally is pushed out by the fixed formula 12 month duration to the first month of 2020 (Jan-20) which in the end provides the wrong totals for both 2019 at $37,425 (Cell: B8) and 2020 (Cell: C8) Project Year's totals (in this example, the 2020 dollar amount does not change due to empty cells from AX13 and beyond).

    I can't seem to figure out the correct formula that I need to utilize the Project Year's date of 2019 and have the Project Month's formula section stretched across the entire span of dates (Cells: B11:AW11) only to include the months in 2019 and exclude the months in 2020, weather it's listed as a Forecast or Actual Cost, and lastly sums up the correct dollar amounts. So, it will now be one standard formula that will work in both of the Project Year's section (Cells: B8 and C8).

    Thank you for your help and please let me know if you need any additional information.

    Garrett
    Last edited by garrett.grillo; 04-25-2019 at 04:19 PM. Reason: Incorrect Characters Were Added to my Write-Up

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 02-15-2018, 03:56 AM
  2. Specified dates as column titles in Bar Graph
    By DigDoug in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-27-2014, 02:30 PM
  3. [SOLVED] Formula to count based on dates and text in multiple cells - See Spreadsheet
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2013, 11:40 AM
  4. COUNTIF & SUMIF formulas needed when decided based on entry into 2 cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-18-2013, 07:58 PM
  5. using VLOOKUP or similar formula to retrieve dates from multiple cells
    By Ducklet64 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-15-2013, 10:10 PM
  6. Using dates and job titles as two criteria in a count
    By Junior_Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 07:54 PM
  7. [SOLVED] Confirming job designators and titles over multiple cells
    By octagonaway in forum Excel General
    Replies: 4
    Last Post: 07-15-2012, 07:07 PM

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