Excel formula: Multiple cells have same value case sensitive

Generic formula

{=AND(EXACT(range,value))}

Explanation

To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. In the example shown, the formula in G5 is:

=AND(EXACT(B5:F5,B5))

This is an array formula and must be entered with control + shift + enter

How this formula works

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.

Author

Dave Bruns

Related formulas

All cells in range are blank

To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. In the example shown, the formula in E5 is: = SUMPRODUCT ( -- ( B5:D5 <> "" )) = 0 How...

Multiple cells have same value

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...

Related functions

Excel EXACT Function

The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.

Excel SUMPRODUCT Function

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...

Excel COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.