CODEWITHSUNDEEP
excel
Hazel
Hazel

Reputation: 85

Excel formula to check if a range of data are all exactly same

I have a range of cell values. I want to check this range of values and return true if all the 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 simoco for the answer which I'm using now. I have a follow-up question. How do I modify this formula to ignore blank cells instead of defaulting to FALSE? With your formula, when it evaluates a range of blank cells (that are waiting for input), it returns "Varies". I want it to return blank instead.

Upvotes: 4

Views: 2392

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this formula:

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

Upvotes: 4

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

Try the following array formula (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

Upvotes: 0

Related Questions