Fix an Excel Array Formula Using a Generated Range
Question details
The user needs an Excel array formula to evaluate cells in a range generated from row and column positions.

- 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.
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.
Use INDEX to create a range reference
Use INDEX to build the range directly without converting text or using volatile INDIRECT.
In Excel 2013, select a cell outside columns B through E on the row you want to check.
In the formula bar, enter =SUM(N(ISBLANK(INDEX($B:$B,ROW()):INDEX($E:$E,ROW())))).
Press Ctrl+Shift+Enter instead of Enter. Excel adds braces around the formula in the formula bar; do not type the braces yourself.
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.

Convert the ADDRESS text to a reference with INDIRECT
Use INDIRECT when you need to keep the existing ADDRESS-based range construction.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Select a result cell: Choose a cell outside columns B through E on the row you want to check.
- 3. Enter the formula: In the formula bar, enter =SUM(N(ISBLANK(INDEX($B:$B,ROW()):INDEX($E:$E,ROW())))).
- 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.

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.




