Countifs and match help needed

Occasional Contributor

Hi all,

I am trying to create a formula that would tell me if any of the names on my first sheet are in sheet 2 or 3. If they are I want a "no" and if they aren't I want a yes.

I tried Countifs and IF(Or) but none of them seem to work. 

Below is the link to the spreadsheet i am trying to use:

https://1drv.ms/x/s!AgSn2Fjy1lHAeoCajIPex8-4AB0?e=Kxin9K

Would someone be able to point me in the right direction please?

Screenshot 2021-05-26 at 11.44.27.pngScreenshot 2021-05-26 at 11.44.34.pngScreenshot 2021-05-26 at 11.44.40.png

6 Replies

@infinity94 

In B2 on Sheet1:

 

=IF(COUNTIF(restr!A:A,A2)+COUNTIF('restrict 2'!A:A,A2),"No","Yes")

 

Fill down.

this worked! thanks so much!
sorry need some more help with this.
updated workbook: https://1drv.ms/x/s!AgSn2Fjy1lHAeoCajIPex8-4AB0?e=KuUzgz
what i am trying to achieve: i have a drop down on the right and if I select Party 1 I want everything that has % free less than 80% and score 10 or above to show as yes, otherwise No.
If i select party 3 I want everything that has % free less than 80% and score 9 or above to show as Yes otherwise as No. can you help me with that please?=IF(C2="No","No",IF(AND(D2<80,E2>O4),"Yes","No"))

@infinity94 

The formula in O4 should be

=IF(O5="Party 1", 10,9)

(The result must be a number, not a text value).

And in F2:

=IF(C2="No","No",IF(AND(D2<80,E2>=$O$4),"Yes","No"))

or slightly shorter

=IF(OR(C2="No",D2>=80,E2<$O$4),"No","Yes")

Fill down from F2 to F4.

@Hans Vogelaar 

thank you! how do i add Party 2= 10 and party 3 equals 9 and 10?

=IF(O5="Party 1", 10,9)

if i use the above, then it will either be 9 or 10 but i need both so would be better to put greater or =9?

Screenshot 2021-05-26 at 14.38.13.png

@infinity94 

Change the formula to

 

=IF(O5="Party 3",9,10)