A couple of formulas having problems with

[Alison Flynn]

Hi guys,

I have two formula's I'm having problems with.

One is with CountIF - I'm not sure it's the right formula to be using.  I have a range of cells containing "Yes" or "No". I'd like the answer to be "Referral" if any of the cells have a "Yes" (regardless to have many yeses - one yes is enough to return a "Referral") in them and "No" if all of them are "No".  

The only way I've seen how to do it is using CountIF in one column =COUNTIF(A2:L2,"Yes") and =IF(M2>=1,"Referral","No") in another column - so I have the answer but I'd rather get it from one formula in one column as opposed to two.  Any ideas?

The second formula I'm having trouble with is as follows:

=IF(AG2=1,IF(AH2=1,"Referral","No")) - This formula is working, but it's returning a 'False' if both values in AG2 and AH2 are zero.

I've tried:

=IF(AG7=1,"Referral",IF(AH7=1,"Referral","No"))

This is not completely working - it's giving a "Referral" if either AG2 or AH2 are zero.  I need a formula that will return "Referral" if both AG2 = 1 and AH2 = 1 and "No" if either one or both are zero.

I hope this makes sense!

Thanks for having a go, much appreciated.

[#2]

Alison,

You're on the right track, you just have to nest the Countif formula inside the If formula like:

=IF(COUNTIF(A2:L2,"Yes")>0,"Referral","No")

Edit - 

I completely missed the second part of your original post, and apologize.   For your second question, try this

=IF(AND(AG7=1,AH7=1),"Referral","No") 

[#3 · Accepted solution]

Hi Bob,

You're a genius!  Thanks a million.  Both worked beautifully.

Alison