COUNT Formula

[dgavin]

I trying to get my formula to count 2 different results...

In sheet2

In sheet 2 column A are names and in B are results e.g

Chris "PASS"
Chris "FAIL"

If chris has a PASS and a Fail result, then my formula will give "STOP" if not then blank

I have tried write it but need help?

=IF(T5>0,"CLEAR",IF(AND(COUNT(Sheet2!$A$2:$A$5000,"Chris")+COUNT(Sheet2!$B$2:$B$5000,PASS+FAIL)=1,H5>0,STOP,"")

 

[bryonwoods30]

in column c use the formula =A1&" "&B1 to combine the cells together from a and b, so you get a single result "chris pass" or "chris fail"

then in another cell use the formula
=IF(AND(COUNTIF(C:C,"chris pass")>=1,COUNTIF(C:C,"chris fail")>=1),"stop","")

 

[dgavin]

Thanks Bryon,

to amend the formula slightly,

if the names are changing all the but i still wanted to count if one person had both a pass and a fail. would i need add 2 vlookups?

the names are alway on sheet1 so if

if chris is in sheet1 column A and is found in sheet2 column A, and has Pass and Fail in sheet2 column B

how would i write this formula?

 

[bryonwoods30]

if the names are in column A so chris is in a1, then do as before. on sheet 2, combine the name and pass/fail in column c with the a1&" "&b1 and drag it down.
then in sheet1 column b next to your names use the formula
=IF(AND(COUNTIF(Sheet2!C:C,A1&" pass")>=1,COUNTIF(Sheet2!C:C,A1&" fail")>=1),"stop","")