Consistency Check

[ekhawaja]

Hello,

I am looking for a formula in col F that checks for consistency by looking up the value from col E , and matching in col B with criteria in col A. If for all the values say "yes" in col A, i would like col G to return yes, otherwise "No" if all of them don't say yes. Data is 1000 of rows that i need to do it on. Thanks in advance.

.

Qty	SS NUMBER			SS NUMBER	Qty Consistency (this is where I need the formula)
Yes	1222706715​			1222706715​	no
Yes	1222706715​			1222534595​	no
	1222706715​			1222638861​	No
	1222706715​			1222705940​	yes
No	1222706715​			1222705941​	yes
Yes	1222706715​
	1222534595​
	1222534595​
Yes	1222534595​
	1222534595​
No	1222534595​
	1222638861​
Yes	1222705940​
Yes	1222705940​
	1222638861​
Yes	1222705941​
Yes	1222705941​

l

 

[Fluff]

How about

=IF(COUNTIFS(B:B,E2,A:A,"<>yes"),"No","Yes")

 

[Scott Huish]

=IF(COUNTIFS(B:B,E2,A:A,"<>Yes"),"No","Yes")

Copy down.

Book1
	A	B	C	D	E	F
1	Qty	SS NUMBER			SS NUMBER	Qty Consistency (this is where I need the formula)
2	Yes	1222706715			1222706715	No
3	Yes	1222706715			1222534595	No
4		1222706715			1222638861	No
5		1222706715			1222705940	Yes
6	No	1222706715			1222705941	Yes
7	Yes	1222706715
8		1222534595
9		1222534595
10	Yes	1222534595
11		1222534595
12	No	1222534595
13		1222638861
14	Yes	1222705940
15	Yes	1222705940
16		1222638861
17	Yes	1222705941
18	Yes	1222705941
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IF(COUNTIFS(B:B,E2,A:A,"<>Yes"),"No","Yes")

 

[ekhawaja]

How about

=IF(COUNTIFS(B:B,E2,A:A,"<>yes"),"No","Yes")

Thanks @Fluff . I noticed the formula is returning "yes" if the value exists in col E, but doesn't exist in col B, anyway to modify the formula to return "Not Found"?

 

[Fluff]

How about

=IF(COUNTIFS(B:B,E2)=0,"not found",IF(COUNTIFS(B:B,E2,A:A,"<>yes"),"No","Yes"))

 

[ekhawaja]

How about

=IF(COUNTIFS(B:B,E2)=0,"not found",IF(COUNTIFS(B:B,E2,A:A,"<>yes"),"No","Yes"))

THANKS ALOT @Fluff , this one seems to be working accurately! Happy Holidays.

 

[Fluff]

Glad we could help & thanks for the feedback.