# Check Cells for Equality – Follow Up [Quick Tip]

Posted on October 16th, 2012 in Huis , Posts by Hui , Quick Tip - 19 comments

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.

 Last day to join Excel School + Excel Hero Academy Please help me design our new product: Vitamin XL
 Written by Hui... Tags: =, Excel 101, excel basics, Microsoft Excel Formulas, quick tip 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 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?

• Stephen says:

^ is "to the power of
e.g. 2*2 could be written as 2^2
2*2*2 could be written as 2^3

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.

• Andrew says:

Ah must be in 2007+. It no worky in 2003

• Leonid K. says:

@Andrew

Did you install Analysis ToolPak?

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}

• Quotenjunkie says:

sry this was wrong, but the following should work
{=MIN((E15:E19=E15)*1)}

13. Ankit says:

Thanks man. You are a live saver 😀

 Last day to join Excel School + Excel Hero Academy Please help me design our new product: Vitamin XL