+ Reply to Thread
Results 1 to 4 of 4

Formula assistance needed to sum data based on anniversary date and month date

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Formula assistance needed to sum data based on anniversary date and month date

    I'm looking for a formula that would look at cell A1 which identifies the month and year for the particular worksheet and then have it look at the anniversary date of the employee affiliated with the particular row and then add up the hours up to the anniversary date (if applicable) and total it in column "BN". I'd like a similar formula in column "BO", but would add only the post anniversary hours (if applicable to the employee for the given month).

    I've attached a sample workbook to help explain what I need. Complicating this formula is the fact that I have duplicate columns with similar dates, which would need to be added if the header number (row 1) is less than or equal to the anniversary date or greater than the anniversary date for the second formula required. Though the sample workbook only reflects one sheet for the month of January, my actual workbook would have a sheet for each month and on each sheet cell A1 would reflect the 1st day of that given month for that sheet. I hope this all makes sense.

    *edited to add: my initial sample workbook example had incorrectly stated to include up through the anniversary date, when the pre-anniversary data should only include date up to the date. A revised workbook was attached.

    Any assistance would be greatly appreciated.
    Attached Files Attached Files
    Last edited by lilsnoop; 11-28-2012 at 10:42 PM. Reason: sample workbook explanation corrected

  2. #2
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Formula assistance needed to sum data based on anniversary date and month date

    Well.. after much reflection and surfing the net I was able to resolve my request. I've attached my sample workbook for those interested in the resolution. I do get a syntax "false" I'd like to either show up as a zero or else leave the cell blank, but otherwise it works great. Hope it can help someone.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Formula assistance needed to sum data based on anniversary date and month date

    Hi lilsnoop,

    Try this formula instead...
    =IF(MONTH(A2)=1,SUMPRODUCT((A2>$D$1:$BM$1)*(D2:BM2)),0)

    You could have used this formula..
    =IF(MONTH(A2)=1,SUMPRODUCT((A2>$D$1:$BM$1)*(D2:BM2)),"")
    but this "ruins" your other formulas...

    So I suggest you use the first formula... if you don't like seeing the 0, then just change your number format... from General to a Custom format and change the type to this... _(* #,##0.00_);_(* (#,##0.00);; (include the two semicolons at the end to not show the zero...

    Let me know if this works...

    Dennis

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Formula assistance needed to sum data based on anniversary date and month date

    Thanks djapigo!! I prefer the "0" to "False" any day! Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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