check if all values returned in filter are equal (google sheets)-CodePudding

I have a row with the following values in each cell from A1 until F1

A,A,A,A,A,A

Then in row 2 I have

A,B,A,A,A,A

Since some columns are empty (no value) I filter the row to get only cells with value:

=filter(A1:F1, A1:F1<>"")

then how can i get a true/false response if all values/strings in the filter array are equal?

CodePudding user response：

Similar answer given here, but that was for a set of arbitrary cells, not a range.

Using range notation:

=COUNTUNIQUE(A1:F1)=1

As implied by the function name, it counts the number of unique values. If there is only one unique value, then we know all the cells are equal. Since COUNTUNIQUE disregards blank values, there is no need to use FILTER first. If you want TRUE for all blanks, change = to <=, as it will return 0 in that case.

If you later decide you do want to consider blank values:

=COLUMNS(UNIQUE(A1:F1, 1))=1

This counts the number of columns returned by UNIQUE(..., 1) (1 means "by column"). If it's 1, then all cells are equal.