Forum Discussion
A couple of formulas having problems with
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.
Hi Bob,
You're a genius! Thanks a million. Both worked beautifully.
Alison
2 Replies
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")
Hi Bob,
You're a genius! Thanks a million. Both worked beautifully.
Alison
