Excel array formula to check when all columns match, but ignore blanks

Tonight's tip of the night explains how you can use an array formula in Excel to confirm that values on a single row for multiple columns are the same, but discount any instances where a column has a blank entry. So if the same value to filled in one or more of the columns in the given range, and none of the other columns has a different value, or simply has a blank value, the result of the array formula will be TRUE.

In this example we have both numbers and text entered in columns B to E. The formula:

=(SUM(IFERROR(1/COUNTIF(B2:E2,B2:E2),0))=1)

. . . will check columns B to E on row 2. This is an array formula, so be sure to press CTRL + SHIFT when entering it to enclose it in curly brackets.

As you can see, regardless of whether numbers or text are use in a row range, the formula indicates if they match or differ, and skips over the cells with no content.