Multiple Cells Have Same Value Case Sensitive

Explanation

This formula uses the EXACT formula to compare a range of cells to a single value:

=EXACT(B5:F5,B5)

Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE values:

{TRUE,FALSE,TRUE,TRUE,TRUE}

This array goes into the AND function, which returns TRUE only if all values in the array are TRUE.

Ignore empty cells

To ignore empty cells, but still treat non-empty cells in a case-sensitive manner, you can use a version of the formula based on SUMPRODUCT:

=SUMPRODUCT(--(EXACT(range,value)))=COUNTA(range)

Here, we count exact matches using the same EXACT formula above, get a total count with SUMPRODUCT, and compare the result to a count of all non-empty cells, determined by COUNTA.

This is an array formula but control + shift + enter is not required because SUMPRODUCT handles the array natively.

Explanation

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.