CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformulaequalityworksheet-function
d3pd
d3pd

Reputation: 8315

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

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?).

Upvotes: 25

Views: 92374

Answers (5)

Balaji
Balaji

Reputation: 9

If you use range selection use below formula

=ARRAYFORMULA(AND(C2=C3:C27))

If you use selected cell to compare use below formula

=ARRAYFORMULA(AND(F2={F3, F4, F5}))

Upvotes: -1

Jeremy Laing
Jeremy Laing

Reputation: 1

If you know what they should all equal then you could use the formula =if(sum(ARRAYFORMULA(if(range = target_value,1,0))) = COUNTA(range),true_value, false_value)

  • range: the range to search through
  • target_value: what you want them to match as (in my case i wanted to know when all the values were "Unlocked")
  • true_value: the value to display when they all match the target value
  • false_value: the value to display when they dont all match the target value

Upvotes: 0

jfursedon
jfursedon

Reputation: 11

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")

Upvotes: 1

wivku
wivku

Reputation: 2683

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(...)

Upvotes: 20

ZygD
ZygD

Reputation: 24406

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

Upvotes: 36

Related Questions