Formula for identifying equal values

[LeeAsh]

Hi I am hoping you can help me with a formula for a friend, he basically has four specific cells and he needs them to become highlighted if all four show the same value, is this possible at all?

Thanks in advance.

[Ace_XL]

In Conditional Formatting use

=COUNTIF($A$1:$D$1,$A$1)=4

[Teethless mama]

Conditional Formatting:

=COUNTIF($A$2:$D$2,$A$2)=4

[LeeAsh]

Thanks for the help, he says:

I have entered =countif($E$21;$E$24;$E$27;$E$30)=$I$20

and it is saying I have entered too few arguments

Any idea where he is going wrong?

[BB1972]

Why is he entering that? Has he tried the solutions provided by Ace & Teethless mama?

[LeeAsh]

He is going to sign up and reply directly, saves me being a go between but thanks for your help everyone.

[RobWatson]

Hi Everyone

I am Rob and Lee has been asking this question for me as he was someone who I reached out to for help because he is the man that can. But I seemed to have stumped him.
I am working on an excel file where I wish to set a rule in the conditional formatting that will allow me to highlight four cells indicating that the data entered in them is the same. I only want it to highlight all of the 4 cells when the value is the same is this possible or not? I am not an excel wizard and I would really appreciate some help as I am a little bit stuck.

[Tony Valko]

Give us the addresses of the cells in question.

If all 4 cells are empty they would all be equal but I suppose you wouldn't want them to be highlighted if/when that occurs?

[martindwilson]

=countif($E$21;$E$24;$E$27;$E$30)=$I$20 you cant use countif with a dis-contiguous range
=AND(E21=E24,E21=E27,E21=E30,E24=E27,E24=E30,E27=E30)

[BB1972]

Hi rob, welcome to the forum. Have you tried either of the solutions already provided?


EDIT: Good point regarding non-contiguous cells, Martin.

[RobWatson]

Yeah I also want them to not be highlighted when they are blank but I have another conditional formating set to highlight them a seperate colour when they are empty and also I need them not to highlight when three of them, or two of them are the same.

The cells I am using are E21 E24 E27 and E30.

Am I asking the impossible?

[RobWatson]

Excel.png This is my cell layout. I have highlighted the four cells that I need to highlight if they are all the same value. The red cell is where I have the text that will be filled in the field

[Tony Valko]

Try this...

=AND(E21<>"",E21=E24,E24=E27,E27=E30)

[RobWatson]

I am getting a 'formula contains error' message for all of these. I am pasting the solutions you give into the conditional formatting. Highlight cell rules, more rules, use formula. I am putting this in the right one?
As I say I am very new at using these rules.

[martindwilson]

use ; instead of ,
=AND(E21<>"";E21=E24;E24=E27;E27=E30)

[RobWatson]

Ive tried this and still no joy.

[Tony Valko]

Here's a small sample file that demonstrates this.

RobWatson.xls

[RobWatson]

Thanks Tony. I understand where I was going wrong. I was trying to add the formula to the four cells.

[Tony Valko]

Good deal. Thanks for the feedback!

[RobWatson]

Hi everyone.

Thanks for the help last week, I really appreciate it.

I have another couple of questions regarding the same spreadsheet and the problems also if you know a good place for me to learn how to use the more complex formulas for excel I really would like to understand them, as the solution I still don't understand even though it works.

In the same spreadsheet I would like to make a similar set of four cells highlight if three out of the four have the same values, is this possible to do using a similar formula?

[martindwilson]

for 3
=OR(AND(E21=E24,E24=E27),AND(E21=E27,E21=E30),AND(E24=E27,E24=E30),AND(E21=E24,E24=E30))
if 3 ane blank and you dont want to include that
=IF(COUNTA(E21,E24,E27,E30)<3,FALSE,OR(AND(E21=E24,E24=E27),AND(E21=E27,E21=E30),AND(E24=E27,E24=E30),AND(E21=E24,E24=E30)))

[BB1972]

@Rob,

... as the solution I still don't understand even though it works. ...

Tony's solution:

=AND(E21<>"",E21=E24,E24=E27,E27=E30)

basically says that for the formula to evaluate to TRUE, the following conditions must be met:

E21<>"": E21 must not be blank (literally, E21 not equal to (<>) blank / an empty string (""))
E21=E24: E21 must equal E24
E24=E27: E24 must equal E27
E27=E30: E27 must equal E30

The "AND" at the start of the formula means that ALL of the conditions must be true.


Therefore, logically, if E21 is equal to E24, and E24 is equal to E27, and E27 is equal to E30, then the 4 cells must be the same as each other. Does this help?