If THEN

**Bilabu1** · 06-26-2018, 10:33 AM

Hi,

I have spreadsheet where I have following problem:

I need to fill is a column in a sheet with contacts. In this column I need to write the word “yes” or “no”. The data comes from different sheet within the same workbook. In this sheet the contacts are sorted by company. These companies have related contacts. These contacts are partners, Associates.n. The answer yes is correct if one of the related contacts is not in Frankfurt. If all related contacts have “Frankfurt” then the answer is “no”. So I have contacts with more than one associated firm contact. So I need a function to read out more than one row and give out a result from these.

Any questions just let me know

**than_gold** · 06-26-2018, 10:39 AM

you can use IF function comparing those cells.

**Sam Capricci** · 06-26-2018, 10:42 AM

possibly a countif formula, but to get a better more accurate response posting a sample sheet with no sensitive / private information, representative data and expected results will get you a better answer. Click on the go advanced button below this box, then manage attachments and follow the prompts to upload a sample sheet.

**Bilabu1** · 06-26-2018, 11:06 AM

Hi,

I have attached an example. The column J has to filled with "yes" or "no". The criteria is column I. If the company has one or more related contacts that are not in Frankfurt the answer is "yes" otherwise "no"

**Sam Capricci** · 06-26-2018, 11:48 AM

You really didn't put in your expected results as I mentioned in post #3. So I'm guessing but this may work for you and I am basing it on columns G and I because you mentioned "related contacts" which appears to be column G and you mentioned column I.

=IF(COUNTIFS($G$2:$G$20,G2,$I$2:$I$20,"<>&Frankfurt")<>COUNTIFS($G$2:$G$20,G2,$I$2:$I$20,"Frankfurt"),"yes","no")

adjust the ranges to suit.
More clarity would be helpful to better refine a formula.

**Bilabu1** · 06-26-2018, 01:13 PM

Hi,

thanks for your help. The expected results are "yes" if one of the related contacts is not in Frankfurt. Otherwise the expected result is "no"

**FDibbins** · 06-26-2018, 01:17 PM

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional

)

**Sam Capricci** · 06-26-2018, 01:20 PM

You're welcome. Then, based on your last post I assume the issue is resolved because that formula appears to work if one of the locations is Frankfurt and if it isn't.
Don't forget to mark the post as solved using the thread tools dropdown at the top of the post.

If the issue isn't resolved then more clarity on which columns to base the formula on is what is needed. As I noted I'm guessing it is based on columns G and I.
AND more examples WITH expected results would help.

EDIT: Hi Ford, I was typing the post while you posted that note.