This is an array formula and must be entered with control + shift + enter Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE values: 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: 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.
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.
