# Countifs and match help needed

Occasional Contributor

# Countifs and match help needed

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?

6 Replies

# Re: Countifs and match help needed

In B2 on Sheet1:

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

Fill down.

# Re: Countifs and match help needed

this worked! thanks so much!

# Re: Countifs and match help needed

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"))

# Re: Countifs and match help needed

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.

# Re: Countifs and match help needed

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?

# Re: Countifs and match help needed

Change the formula to

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