Multiple columns are equal

In the example shown, we want to test if all values in each row are equal. To do this, we use an expression that compares the value in the first column (B5) to the rest of the columns (C5:F5):

B5=C5:F5

Because we are comparing one cell value to values in four other cells, the result is an array with four TRUE or FALSE values. In row 5, all values are equal, so all values are TRUE:

{TRUE,TRUE,TRUE,TRUE}

This array is returned directly to the AND function, which returns TRUE, since all values in the array are TRUE.

=AND({TRUE,TRUE,TRUE,TRUE}) // returns TRUE

In cell H6, B6=C6:F6 creates an array with two FALSE values, since D6 and F6 are different.

{TRUE,FALSE,TRUE,FALSE}

This array is delivered to the AND function, which returns FALSE:

=AND({TRUE,FALSE,TRUE,FALSE}) // returns FALSE

Counting differences

The formula in I5 uses the COUNTIF function to count differences in each row like this:

=COUNTIF(C5:F5,"<>"&B5)

The criteria is provided as "<>"&B5, which means "is not equal to B5".

You can adjust the formula to mimic the behavior of the AND formula above like this:

=COUNTIF(C5:F5,"<>"&B5)=0

Here, we simply compare the result from COUNTIF to zero. A count of zero returns TRUE, and any other number returns FALSE.

This is not an array formula, so it does not require special handling.