Find a sum based on a date range and an aditional value

    Hello all,


    I have a project I am working on and I am stumped at the current time.


    I am trying to get a total value to populate on sheet Labor in cells E6 THRU L6.


    This value should be determined by the dates above it i.e. the total should be all values between these two dates. I also need to break it up by a work cell in this case it should only be looking for what is in A2 on the Labor sheet.


    I have attached a sample of what I am working on and the formula I have been trying is this:


    Code
    =SUMIFS(Vacation!$B:$AAY,Vacation!$B:$AAY,"<="&Labor!E5,Vacation!$B:$AAY,">="&Labor!E4,Vacation!$A:$A,"="&$A$2)


    however this only gives me a #VALUE!


    Any help would be great as I have been stuck for about a week on this.


    Thanks!

    Re: Find a sum based on a date range and an aditional value


    Hello,


    If you do not mind ... just a couple of remarks ...


    1. You should start by getting rid of ALL you merged cells ... They will only prevent you from working ...:evil:
    If you are in need for a visual centering ... you can use Alignment>Horizontal>Center Across Selection ...


    2. For your formulas ... use specific ranges from A2:A48 ... otherwise you are into over a million rows for each column ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Find a sum based on a date range and an aditional value


    Thanks for the help but it didn't fix anything. I did change the merged cells and used the Center Across Selection but I am still coming up short on how to get this to work the way I need it to.

    Re: Find a sum based on a date range and an aditional value


    Quote from avb85;788985

    Thanks for the help but it didn't fix anything. I did change the merged cells and used the Center Across Selection but I am still coming up short on how to get this to work the way I need it to.


    Glad you have removed all merged cells ...:smile:


    The issue is to have your worksheet presented like a DataBase ... following the best practices ...


    see http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login