Results 1 to 3 of 3

Thread: Formula Help

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Formula Help

    Hi Loungers - after some help again.

    I need to be able to count on multiple criteria in Sheet 2 from the data in sheet1. I need to be able to count on Project and then by number of weeks indicated in the cells.

    I managed to get the count on one criteria Colum b on sheet 2 but canít get the rest to work.

    Hope that makes since - any assistance would be greatly appreciated

    Regards
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Countif can only do 1 criteria. If you have XL2007 or later you can use COUNTIFS:
    In Sheet2! F7:I7 (did you make a mistake, you have none = 4...)
    =COUNTIFS(Sheet1!$B$2:$B$211,$D7,Sheet1!$J$2:$J$21 1,1)
    =COUNTIFS(Sheet1!$B$2:$B$211,$D7,Sheet1!$J$2:$J$21 1,2)
    =COUNTIFS(Sheet1!$B$2:$B$211,$D7,Sheet1!$J$2:$J$21 1,3)
    =COUNTIFS(Sheet1!$B$2:$B$211,$D7,Sheet1!$J$2:$J$21 1,">4")

    If you have XL that is <2007, you need to use SUMPRODUCT:
    In Sheet2! F7:I7
    =SUMPRODUCT((Sheet1!$B$2:$B$211=$D7)*(Sheet1!$J$2: $J$211=1))
    =SUMPRODUCT((Sheet1!$B$2:$B$211=$D7)*(Sheet1!$J$2: $J$211=2))
    =SUMPRODUCT((Sheet1!$B$2:$B$211=$D7)*(Sheet1!$J$2: $J$211=3))
    =SUMPRODUCT((Sheet1!$B$2:$B$211=$D7)*(Sheet1!$J$2: $J$211>4))

    After entering in the 4 formulas, copy them down the column

    [Note: instead of explicitly listing the 2nd criteria, you could put it into a cell and have the same formula in all of them. Something like:
    =SUMPRODUCT((Sheet1!$B$2:$B$211=$D7)*(Sheet1!$J$2: $J$211=F$4))
    where f4:H4 would have 1,2,3]

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve - thanks very much for you assistance.

    Works just fine

    Regards

Posting Permissions

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