if same value in a range, return yes

[prochasska]

Hi,

I have a big table where I need a formula to do the following:

• if six cells in a column contain the same value, the formula should return "yes"

Example:

[TABLE="width: 99"]
<colgroup><col></colgroup><tbody>[TR]
[TD]column N[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
</tbody>[/TABLE]

• if six cells in a column contain different values, the formula should return "no"

Example:

[TABLE="width: 99"]
<colgroup><col></colgroup><tbody>[TR]
[TD]column N[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Barcelona[/TD]
[/TR]
[TR]
[TD]Barcelona[/TD]
[/TR]
[TR]
[TD]Rome[/TD]
[/TR]
</tbody>[/TABLE]

I have tried to use if and countif, but the formula returns "yes" only for the first cell. I need the formula to return yes for all six cells.

I hope someone can help with this formula.

 

[Fluff]

Duplicated https://www.mrexcel.com/forum/excel-...eturn-yes.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.

 

[Fluff]

Closed