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

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?

=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. 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.

Thank you and see you around.

### Related articles:

 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:

={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. James Travers says:

^ 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.

• Leonid K. says:

COUNTDIFF is not Excel build in function, but part of free MoreFunc add-in.
A free library of 66 new worksheet functions for Excel 95 or above.

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 😀

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.