How can I check if the values of multiple cells are equal?

Question

Let's say I have 6 different cells (that are not all in a line). I want to check if the values in these cells are equal. How could I do this with a function? I'd want the function simply to display "EQUAL" or "NOT EQUAL" (or maybe change the cell background color?).

Solution 1

One option for 6 cells would be this:

=IF(AND(A1=B2,B2=C3,C3=D4,D4=E5,E5=F6),"EQUAL","NOT EQUAL")

Another option - this way you don't need to reference the same cell twice:

=IF(AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6})),"EQUAL","NOT EQUAL")

---

If you wanted to color some cells if values in these cells are equal, you would need to create a Conditional Formatting rule with a similar formula:

1. Select the cells you want to color
2. Format > Conditional Formatting
3. Select "Custom formula is"
4. Fill in one of the above formulas without the IF part of formula, e.g.
   =AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6}))
5. Select the formatting style (color)
6. Done

Solution 2

as formula for conditional formatting:

=countunique({A1,B2,C3,D4,E5,F6})=1

as function it would be similar: =if([formula],"EQUAL","NOT EQUAL"):

update: the requested clarification:

• put the wanted cells in a custom array {A1,B2,C3,D4,E5,F6}
  (delimiters: , = new column, ; = new row; for countunique either is fine)
• get the unique values countunique(...)
• if the outcome is 1 then all values are the same

update 2: original answer used =count(unique(...)) instead of the combined function =countunique(...)

Solution 3

Here's an option without using an array that is easier to implement for large numbers of cells:

=IF(MIN(A1,B2,C3,D4,E5,F6)=MAX(A1,B2,C3,D4,E5,F6),"Equal", "Not Equal")