Note: this is an array formula and must be entered with control + shift + enter, unless you are using Excel 365, where array formulas are native. 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: This array is returned directly to the AND function, which returns TRUE, since all values in the array are TRUE. In cell H6, B6=C6:F6 creates an array with two FALSE values, since D6 and F6 are different. This array is delivered to the AND function, which returns FALSE:
Counting differences
The formula in I5 uses the COUNTIF function to count differences in each row like this: 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: 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.
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.
