Formula Errors

Fix an Excel Array Formula Using a Generated Range

Steve KSteve K Nov 18, 2026 869 views

Question details

The user needs an Excel array formula to evaluate cells in a range generated from row and column positions.

Fix an Excel Array Formula Using a Generated Range
Product
Microsoft Excel 2013
Device & OS
not provided
Scenario
A formula uses ADDRESS and a colon to construct a range in the current row, then checks its cells.
Observed behavior
The formula returns an incorrect result because the generated range is text, not a cell reference.
Before you start

Identify the columns and row your formula should evaluate. The examples below count genuinely empty cells in columns B through E of the current row, so enter them outside that range.

Solution 1Recommended

Use INDEX to create a range reference

Use INDEX to build the range directly without converting text or using volatile INDIRECT.

1
Select a result cell

In Excel 2013, select a cell outside columns B through E on the row you want to check.

2
Enter the formula

In the formula bar, enter =SUM(N(ISBLANK(INDEX($B:$B,ROW()):INDEX($E:$E,ROW())))).

3
Confirm as an array formula

Press Ctrl+Shift+Enter instead of Enter. Excel adds braces around the formula in the formula bar; do not type the braces yourself.

4
Check the result

Compare the result with the empty cells in columns B through E of that row. Change the column references if your target range is different.

Use INDEX to create a range reference
Cells containing formulas: ISBLANK counts only genuinely empty cells. A cell containing a formula that returns an empty string is not considered blank.
Work with spreadsheet formulas

Count blank cells with a generated range in WPS Spreadsheet

Use INDEX in WPS Spreadsheet to create a current-row range reference and count genuinely empty cells without converting ADDRESS text.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Select a result cell: Choose a cell outside columns B through E on the row you want to check.
  3. 3. Enter the formula: In the formula bar, enter =SUM(N(ISBLANK(INDEX($B:$B,ROW()):INDEX($E:$E,ROW())))).
  4. 4. Confirm and verify: Confirm the formula and compare the result with the empty cells in columns B through E. If your WPS version requires legacy array entry, confirm with Ctrl+Shift+Enter.
Build a range reference without volatile INDIRECTOpen and save Microsoft Excel XLSX and XLS filesWork with familiar spreadsheet formulas and a formula bar
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't ADDRESS create a usable range by itself?

ADDRESS returns a cell address as text. Joining two ADDRESS results with a colon still produces text; use INDIRECT to interpret it as a reference, or use INDEX to create the reference directly.

Why should I use INDEX instead of INDIRECT?

INDEX can return references without converting text. Unlike INDIRECT, it is not volatile, so it can avoid unnecessary recalculation in larger workbooks.

How do I enter this array formula in Excel 2013?

Type the formula in the formula bar and press Ctrl+Shift+Enter. Excel displays braces around a successfully entered legacy array formula.

Does ISBLANK count cells whose formulas return an empty string?

No. ISBLANK returns FALSE for a cell containing a formula, even if that formula displays an empty string.