1. ## Formula help

greetings,
i have included data sample and formula description in the attachment.

2. 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.

3. 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....

4. Can you post your new workbook?

5. 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"

6. attached, applying your formula and Maudib version.

7. 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,"")`

8. dubdub,

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

9. 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!

10. OK, seems like I misunderstood the request to look at <3 comps per code.

11. I'm not sure. That's what I assumed, but the sample doesn't clearly state either way.

12. Rory,

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

13. Great Thanks Rory, and thanks as well to Maudibe for the help.

14. 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. 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
•