Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a range of cells and I want a formula that says if all these contains the same values or not.

Usually for two cells I would use if but I want a solution that would work for ranges. I don't want to have 20 IFs one under another.

I'm comparing string values.

share|improve this question
up vote 3 down vote accepted

If you are dealing with a range of known size then you could use CountIf to get the number of times a value appears in the range.

share|improve this answer

If the values are just numbers, you can use the STDEV function - if they are identical, STDEV will be exactly zero.

share|improve this answer

You can use the Exact Function (Text Category, but will work with numbers)

Sort each range the same way. Exact will compare cell to cell and return true if it matches exactly and false if it doesn't. Autofill makes it quick and easy to do to two ranges.

share|improve this answer
This doesn't answer the question as I understand it. OP has a single range and wants to know if all the cells in the range are the same. He is not comparing 2 different ranges. – Shevek Mar 25 '10 at 9:02
I didn't understand it that way, but my answer is still correct. He can use the exact function to compare one cell to all the others and discover if any don't match. Or, he can copy the range and use Exact to compare them both. – Jordan 1GT Mar 26 '10 at 2:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .