Multiple Cells Have Same Value

Explanation

This formula relies on the standard behavior of the COUNTIF function. The range is C5:C8, the criteria is provided as not equals OK:

=COUNTIF(C5:C8,"<>ok")

The COUNTIF then returns a count of any cells that do not contain “OK” which is compared to zero. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE.

Ignore empty cells

To ignore empty cells, you can use a more generic version of the formula:

=COUNTIF(range,value)=COUNTA(range)

This formula generates a count of all matching values and compares that count to a count of all non-empty cells.

Non-contiguous cells

If the cells are not in a contiguous range, you can use the AND function and a more manual approach. For example, to check that cells A1, A3, A5, and A9 contain “OK”, you can use a formula like this:

=AND(A1="ok",A3="ok",A5="ok",A9="ok")

Excel is not case-sensitive by default, so using “OK” or “ok” will return the same result.

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.