CODEWITHSUNDEEP
excel
sigil
sigil

Reputation: 9546

How to check that all non-blank cells in range have same value?

I'm trying to determine whether all non-blank cells in a range have the same value.

This range should return TRUE:

45A
45A

45A
45A

This range should return FALSE:

45A
45B

45A
45A

If the range is blank, it should also return TRUE.

Following this answer, I tried this:

=SUMPRODUCT(1/COUNTIF(L68:L72,L68:L72))=1

This works successfully when every cell in the range has a value, but if there are any blank cells in the range, I get a #DIV/0! error. How can I make this work if there are blank cells?

Upvotes: 2

Views: 2251

Answers (2)

L42
L42

Reputation: 19727

Another way is to suppress the error using IFERROR but you'll have to enter it as array formula by pressing Ctrl+Shift+Enter.

=SUMPRODUCT(IFERROR(1/COUNTIF(L68:L72,L68:L72),0))=1

Upvotes: 2

user4039065
user4039065

Reputation:

Adjust the numerator of your count unique to check for non-blanks and add a zero-length string to the COUNTIFS's criteria arguement.

=SUMPRODUCT((L68:L72<>"")/COUNTIF(L68:L72,L68:L72&""))=1

See Count Unique with SUMPRODUCT() Breakdown for more information.

Upvotes: 2

Related Questions