Geek asks and answers

Excel formula to check if all data is exactly the same

I have a range of cell values. I want to check this range of values ​​and return true if all values ​​are exactly the same and return false if at least 1 is not the same.

Range 1
Confirmed
Confirmed
Confirmed

Result= Confirmed

Range 2
Confirmed
Negotiation
Confirmed

Result = Varies

ETA: Thanks to simoco for the answer I'm currently using. I have the following question. How to change this formula to ignore empty cells instead of defaulting to FALSE? With your formula, when it evaluates a range of empty cells (that are waiting for input), it returns "Varies." I want him to return the void instead.

+3
source share
2 answers

Try this formula:

=IF(COUNTIF(A1:A3,A1)=COUNTA(A1:A3),"Confirmed","Varies")
+3
source

(Ctrl + Shift + Enter)

=IF(SUM(IF(FREQUENCY(MATCH(A1:A3,A1:A3,0),MATCH(A1:A3,A1:A3,0))>0,1))=1,"Confirmed","Varies")

enter image description here

0

More articles:


All Articles