Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Excel 2003
    I have a workbook, where I want to sum data from one tab into another, based on 2 sets of criteria.

    The first tab (where I want everything to sum up at) Has a week ending (w/e) date column(ex: 07/03/09, say in cell B1). & It has variance categories (ex. Ahead of Schedule (say A2).
    The second tab also has a w/e date column (A:A) & the variance categories (B:, & hour values assigned (C:C).

    I want the first tab to sum everything from the second tab as long as the dates & categories match....

    Like a sumif : Sheet1!B1=sheet2!A:A, and Sheet2!B:B="Ahead of Schedule"...If those two criterias are met, I want it to return the sum of hours in sheet2!C:C. Sheet2, column B may have several entries for w/e 7/3/09...with various variance categories. It also may contain other w/e dates, like 6/26/09 or 6/19/09. However, each week, I am only concerned with the previous w/e date...so each week Sheet1!A2 's date will change & I'll need the sum to only show for the new w/e date. That's why I need it referenced to the cell, not an actual date value from the first tab.


    any and all help would be Greatly appreciated!!! Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm not sure I completely understand your description, but you should be able to use a formula similar to this in a cell on Sheet1:

    =SUMPRODUCT((Sheet2!$A$2:$A$1000=B1)*(Sheet2!$B$2: $B$1000=A2)*Sheet2!$C$2:$C$1000)

    where B1 is the cell on Sheet1 with the reference date and A2 is the cell on Sheet1 with the variance category.

    Adjust the bounds 2 ... 1000 as needed.

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is an example of the file I'm working with....
    I'm concentrating to correct the formula on "Wed-Presentation" tab, Row 5 to only calculate the items from "AA3 0801" tab that match the category & w/e date from "Wed-Presentation" tab.

    Row 6 is calculating the same data, just not distinguishing a week ending date.

    --I'm not sure if the formula you provided will do what I need.


    Again, thank you very much!!!!
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use this formula in D5 on Wed-Presentation:

    =SUMPRODUCT((INDIRECT("'"&$B5&" "&$C5&"'!A2:A8")=$A5)*(INDIRECT("'"&$B5&" "&$C5&"'!F2:F8")=D$1)*INDIRECT("'"&$B5&" "&$C5&"'!E2:E8"))

    This formula can be filled right to U5, and it can be copied to D8:U8, D11:U11 etc. I've assumed that you'll enter a date in A8, A11 etc. If not, change $A5 to $A$5 in the formula, all rows will then refer back to A5.

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783634' date='08-Jul-2009 21:07']You can use this formula in D5 on Wed-Presentation:

    =SUMPRODUCT((INDIRECT("'"&$B5&" "&$C5&"'!A2:A8")=$A5)*(INDIRECT("'"&$B5&" "&$C5&"'!F2:F8")=D$1)*INDIRECT("'"&$B5&" "&$C5&"'!E2:E8"))

    This formula can be filled right to U5, and it can be copied to D8:U8, D11:U11 etc. I've assumed that you'll enter a date in A8, A11 etc. If not, change $A5 to $A$5 in the formula, all rows will then refer back to A5.[/quote]


    Yes that worked! Thank you so much Hans!!!! I would have thought I would have indicated the date value & category values & all! Thank you so much!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •