Check Cells for Equality – Follow Up [Quick Tip]

Huis , Posts by Hui , Quick Tip - 19 comments

Last week in Write a formula to check few cells have same value [Homework],

Chandoo posed the question: Can you write a formula to check if a few cells are equal?

 

A lot of people answered with answers like:

=IF(COUNTIF(A1:A4,A1)=COUNT(A1:A4),TRUE,FALSE)

or

=IF(SUM(A1:A4)/COUNT(A1:A4)=A1,TRUE,FALSE)

or

=IF(PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)),”Yes”,”No”)

etc.

Although these are all correct, the formulas can be simplified by using a built in function of Excel

The function is

 

=

In Excel the = sign does two things

1. When the = sign is the first character of a Cell, Named Formula or Reference it tells the Cell, Named Formula or Reference that it contains a formula

2. It acts as an equality function when it has a value or formula on either side of it

 

What does the = function do?

What does the = function do?

The = function returns a Boolean or TRUE / FALSE value depending on the equality of the values/formula on either side of it

Example:

In a blank cell enter =2=1

Excel will display FALSE to tell us that the values aren’t equal

Now change the formula to =2=2

Excel will display TRUE to tell us that the values are equal

 

Either side of the = sign can contain a Formula, Cell Reference etc

=(200/100)=2 will return TRUE

=SIN(PI()/2)=1 will return TRUE

=SIN(PI()/2)=15 will return FALSE

=-1=COS(PI()) will Return TRUE

 

=SIN(PI()/2)=A1 will return TRUE if cell A1 contains either a 1 or a formula that returns the value 1.

It will return False for all other values.

 

So the above, Homework, formulas can be simplified as :

Original: =IF(COUNTIF(A1:A4,A1)=COUNT(A1:A4),TRUE,FALSE)

Simplified=COUNTIF(A1:A4,A1)=COUNT(A1:A4)

or

Original: =IF(SUM(A1:A4)/COUNT(A1:A4)=A1,TRUE,FALSE)

Simplified: =SUM(A1:A4)/COUNT(A1:A4)=A1

or

Original: =IF(PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)),”Yes”,”No”)

Simplified: =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1))

and all will still, correctly, answer the question

 

Challenge:

If your formula started with an If(), see if you can rewrite it based on the above.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Hui...
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

19 Responses to “Check Cells for Equality – Follow Up [Quick Tip]”

  1. Mawdo81 says:

    Sorry but =SUM(A1:A4)/COUNT(A1:A4)=A1 will fail in the case of: {2,1,3,2}
    Sum = 8, Count = 4 Sum/Count = 2 = A1 = True but 2<>1<>3<>2 although 2 =2!

  2. Renjith M Das says:

    Why not something even more simple?
    =AND(G2:G5=H2:H5)
    Ctrl+Shift+Enter

    If you insist on the "If..", then

    =If (And(G2:G5=H2:H5),"True",False")
    Ctrl+Shift+Enter

  3. Renjith M Das says:

    Please ignore above...I misread the question

    Please use the below

    ={If (And(G2:G5=G2),"True",False")}
    Ctrl+Shift+ Enter

    Or

    ={AND(G2:G5=G2)}
    Ctrl+Shift+Enter

  4. Renjith M Das says:

    Went back and saw that the formula I suggested is very similar to the obvious answer...so here is another

    =IF(COUNTA(I4:I7)>SUMPRODUCT(--(I4:I7=I4)),"False","True")

  5. Jorrie says:

    Dan u good ! Learned a new thing today.

    Here's my new formula

    Old Formula: {=IF(SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A),TRUE,FALSE)}

    New Formula: {=SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A)}

  6. Valentin says:

    your formula with the equal sign to check ceels for equality works correctly with numbers but not with letters.

    • Mike Coffey says:

      Should you want to compare cellular equality with letters or text, use the =EXACT(D4,D) The result will be either TRUE or FALSE.

      MC

  7. shairal says:

    Quick question, what does the ^ in
    =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)) do?

     

  8. ^ raises to the power of.
    But that formula fails for {2,2,1,1} Product is 4, countif is 2, 2^2 is 4 but whilst 2=2 & 1=1, 1<>2.

  9. Leonid K. says:

    My solution is: COUNTDIFF(A1:A4,1,)=1
    Excel function COUNTDIFF counts the number of unique values in a range or an array.
    SYNTAX :
    =COUNTDIFF(Array,Blanks,Exclude)
    - Array (range or array) : can contain any type of data (numbers, text, empty cells...).
    - Blanks (boolean, optional) : specifies if empty cells must be taken into account (default : FALSE).
    - Exclude (value, range or array, optional) : value(s) which must be excluded from the count.
    Unlike many posted solutions, this function allows handling blank cells.
     

  10. Andrew says:

    @leonid

    Thank you! this is a great function. Did not know about it. 

  11. jeje says:

    =COUNTIF($A$1:$A$4,A1)>1

    is this correct? not sure if I understood the question correctly. 

  12. Quotenjunkie says:

    At the moment I am trying to learn array formulas,

    is this also correct ?

     {=A1:A4=A1}

  13. Ankit says:

    Thanks man. You are a live saver 😀

Leave a Reply


« »