Hello!
I need help with an IF function in Excel 2007.
I need the IF formula to put a "1" if the word "YT" or "BC" or "Pacific_Ocean" is in cell J2:V2. If none of these words are there, I want the IF formula to put a "0".
I have written the following formula but it contains an error:
=IF(OR(J2:V2="YT",J2:V2="BC",J2:V2="Pacific_Ocean")"1", "0")
Can you please help!
Thanks a lot!
Last edited by XavierC; 04-15-2013 at 02:30 PM. Reason: Solved
Try
=--(SUM(COUNTIF(J2:V2,{"YT","BC","Pacific_Ocean"}))>0)
=IF(OR(COUNTIF(J2:V2, "YT"), COUNTIF(J2:V2, "BC"), COUNTIF(J2:V2, "Pacific_Ocean")), 1, 0)
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
ASnother solution
=IF(SUMPRODUCT((J2:V2="YT")+(J2:V2="BC")+(J2:V2="Pacific_Ocean"))>0,1,0)
Appreciate the help? CLICK *
Try this array formula**:
=--OR(J2:V2={"YT";"BC";"Pacific Ocean"})
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you very much all for your quick reply!
You're welcome, we appreciate the feedback.
Exactly
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks