Summary

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. In the example shown, the formula in C9 is: 

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

Note: this formula is not case-sensitive, you can find a case-sensitive formula here.

Generic formula

=COUNTIF(rng,"<>value")=0

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.

Related formulas

Excel formula: Multiple cells have same value case sensitive

Multiple cells have same value case sensitive

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...
Excel formula: All cells in range are blank

All cells in range are blank

When working with Excel, there are times when you need to determine if a range of cells is empty. This can be useful in various scenarios, such as data validation, error checking, or report preparation. In this article, we'll explore a couple of formulas that can help you check if all cells in a...

Related functions

Excel COUNTIF function

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count...
Excel AND function

AND Function

The Excel AND function is a logical function used to test multiple conditions at the same time. AND returns TRUE only if all the conditions are met. If any conditions are not met, the AND function returns FALSE. The AND function is commonly used with other...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions