Results 1 to 15 of 15

Thread: Formula help

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula help

    greetings,
    i have included data sample and formula description in the attachment.
    Attached Files Attached Files
    Last edited by dubdub; 2015-02-16 at 09:04.
    TIA
    dubdub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming the last three rows should all be 1, as there are only 2 rows of comp for code dd prior to that, the formula would be:

    =IF(AND(OR(C2={"red","del"}),COUNTIFS(C$1:C1,"Comp ",A$1:A1,A2)<3),1,"")
    in D2.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi rory,

    I set three "comp" status for the last code and the formula assigned 1 where it suppose not. Any guidance how to make it not to....
    TIA
    dubdub

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post your new workbook?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    As an alternative, the following formula works well:

    =IF(OR(C2="red",C2="del"),IF(COUNTIF($C$1:$C1,"com p")<3,1,""),"")

    Maud

    note the space intentionally entered in "comp"
    Last edited by Maudibe; 2015-02-16 at 10:44.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    attached, applying your formula and Maudib version.
    Attached Files Attached Files
    Last edited by dubdub; 2015-02-16 at 11:03.
    TIA
    dubdub

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It seems the forum added a space after Comp in my formula, which shouldn't be there. It's:
    Code:
    =IF(AND(OR(C2={"red","del"}),COUNTIFS(C$1:C1,"Comp",A$1:A1,A2)<3),1,"")
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    dubdub,

    Please take the space out of "com p" that gets unintentionally added when posting
    Attached Files Attached Files

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Also note that our formulas do not do the same thing. Mine assumes the three "comp" values must be for the same code in column A as the current row, whereas Maud's just assumes any three "comp" values. It is not clear from the example which is correct!
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    OK, seems like I misunderstood the request to look at <3 comps per code.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'm not sure. That's what I assumed, but the sample doesn't clearly state either way.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Rory,

    Looking at his sample, rows 9 and 10 have a 1 in the flag column therefore yours was the correct interpretation.

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Great Thanks Rory, and thanks as well to Maudibe for the help.
    TIA
    dubdub

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..I get confused over here. But then, over there, you drive on parkways, and you park on driveways. And don't get me started on 'elevated subways' - our London Underground is named for a very good reason.
    Just thought I'd mention that.

    zeddy

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Must have got it from our forefathers

Posting Permissions

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