Forum Discussion

infinity94's avatar
infinity94
Copper Contributor
May 26, 2021

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?

excel
Formulas and Functions

6 Replies

    • infinity94's avatar
      infinity94
      Copper Contributor
      May 26, 2021
      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"))
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 26, 2021

        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.

Resources