Excel Array Length: Count Elements, Rows, and Columns

How-to basics · Updated November 18, 2026

To find the number of elements in a rectangular Excel array, multiply its row count by its column count: use =ROWS(A1:C4)*COLUMNS(A1:C4) for the range A1:C4, which contains 12 elements. For a one-dimensional array, use ROWS for a vertical array or COLUMNS for a horizontal one.

What array length means in Excel

Excel does not have a general worksheet function named ARRAYLENGTH or LENGTH that returns an array’s number of elements. Instead, its ROWS and COLUMNS functions report the dimensions of an array or range. Multiplying those dimensions gives the total number of positions, including positions that may be blank.

For example, a 4-row by 3-column array has 12 positions. A vertical list with 8 rows has 8 elements, and a horizontal list with 8 columns also has 8. The distinction matters because an array can be one-dimensional or two-dimensional, and the word length may refer to either the number of items or the size of a particular dimension.

Excel worksheet formulas work with rectangular arrays: every row has the same number of columns. Thus, for a normal rectangular array, multiplying the dimensions gives its total element count. If you mean the number of nonempty cells rather than the number of positions, use a counting function instead; those are different questions.

Count elements in a range

For a fixed range, use ROWS and COLUMNS with the range as the argument. With values or blanks in A1:C4, the formula =ROWS(A1:C4)*COLUMNS(A1:C4) returns 12 because the range has four rows and three columns. It counts the range’s shape, not how many cells contain data.

This approach is useful when a formula needs to know the capacity or dimensions of a block. For example, a 10-row by 5-column input area has 50 positions even if only 37 have been filled. That result can be useful for checking expected array dimensions, but it should not be described as a count of populated records.

To get just one dimension, leave out the multiplication. With the range A1:C4, =ROWS(A1:C4) returns 4, while =COLUMNS(A1:C4) returns 3. These functions accept ranges and array results, so they can also be used as building blocks inside larger formulas.

Find the size of a spilled dynamic array

In Microsoft 365, a dynamic-array formula can return results into neighboring cells, called its spill range. The spill operator # refers to the entire current result, starting from the formula cell. If a formula in A1 spills to a rectangular area, and you want the number of positions in that result, use =ROWS(A1#)*COLUMNS(A1#). The result changes when the spill range changes size.

For example, suppose A1 contains a formula that spills into three rows and four columns. With that spilled result anchored at A1, =ROWS(A1#)*COLUMNS(A1#) returns 12. The formula counts the output’s dimensions; it does not count the number of distinct values or exclude blanks in the output.

The spill operator is available in current Microsoft 365 Excel for Windows and works in supported current Excel for Mac versions as well. It is designed for dynamic arrays, so it is not a general way to refer to the output of every older, fixed-size array formula. If a formula is not a spill formula, use its actual source range or the array expression directly where the function supports it.

Count a vertical or horizontal array

For a single-column array, the row count is its length. With the values in A2:A9, =ROWS(A2:A9) returns 8. For a single-row array in A2:H2, =COLUMNS(A2:H2) returns 8. Choose the dimension that varies across the list, rather than multiplying both out of habit.

For a one-dimensional dynamic result, the same rule applies. A result that spills down a column is measured with ROWS; one that spills across a row is measured with COLUMNS. If you are unsure of its orientation, multiply both dimensions. For a vertical array of 8 by 1, the product is 8; for a horizontal array of 1 by 8, it is also 8.

Some functions can return arrays whose orientation depends on their inputs or options. Check the actual result shape rather than assuming the array is vertical. This is especially helpful when building formulas that combine results from multiple sources, because a change from a row to a column can affect downstream calculations even when the total number of values stays the same.

Array length versus nonblank count

A common source of confusion is using COUNTA to measure array length. COUNTA counts cells or values that are not empty; it does not report the dimensions. If A1:C4 contains three rows of data, one entirely blank row, and other blank cells, the range still has 12 positions, while COUNTA returns the number of entries it considers nonempty.

Choose the function based on what you need:

  • Total positions: multiply ROWS by COLUMNS.
  • Nonempty entries: use COUNTA on the relevant range.
  • Numeric entries: use COUNT, which counts numbers rather than all nonempty content.
  • Text character count: use LEN on a text value; it measures characters, not array elements.

These measures can legitimately differ. A cell containing a formula that displays an empty string may be treated as nonempty by COUNTA, even though it looks blank. Conversely, a truly empty cell contributes to the range’s dimensions but not to the nonempty count. If the data can contain errors or formulas returning empty text, test the counting method against those cases before relying on it.

Count the items returned by a function

Many modern Excel functions return arrays. In Microsoft 365 and Excel 2021, functions such as FILTER, SORT, UNIQUE, and SEQUENCE can produce results whose size depends on the formula or data. To measure a returned array, apply ROWS and COLUMNS to that array, or measure its spilled range with the spill operator.

For a concrete illustration, if SEQUENCE is set to return 5 rows and 2 columns, its result has 10 positions. With an array expression that returns those dimensions, multiplying ROWS by COLUMNS gives 10. The function name alone does not tell you the final size in every case: a FILTER result, for instance, can have fewer rows when fewer records meet its condition.

Version availability matters. The functions FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, XMATCH, and LET require Microsoft 365 or Excel 2021 or later. If a workbook is opened in an older Excel release, those functions may not be recognized. ROWS and COLUMNS, by contrast, are long-standing worksheet functions and are suitable for ordinary ranges and many array calculations in older versions too.

Array dimensions and worksheet limits

A worksheet in modern Excel has 1,048,576 rows and 16,384 columns. Those limits describe the grid, not a promise that every formula can return an array covering the entire grid. A spill result also needs empty cells in its destination area; occupied cells or the worksheet edge can prevent it from expanding.

If a dynamic-array formula cannot place its result, Excel may display a #SPILL! error. Check whether cells in the intended spill area contain data, merged cells, or other obstructions, and whether the required output would extend past the worksheet boundary. Measuring the intended array’s dimensions can help diagnose its expected size, but it does not clear obstructions or make an oversized result fit.

Very large ranges can also make a workbook slower, particularly when many formulas repeatedly process them. When possible, refer to the actual data range or a well-defined Excel table column rather than an unnecessarily large area of the sheet. The correct dimensions are not only about getting the count right; limiting calculation to relevant data can improve responsiveness.

Choosing the right method

Start by deciding whether you need shape, total positions, or populated entries. Then select the simplest function that matches that meaning:

  1. For total elements in a rectangle, multiply its row count by its column count.
  2. For the length of a column, use ROWS; for the length of a row, use COLUMNS.
  3. For a dynamic spill result, refer to the anchor cell with #, then measure its dimensions.
  4. For filled cells rather than positions, use COUNTA or COUNT, depending on whether text should count.
  5. For characters in text, use LEN; do not confuse text length with array length.

One practical check is to compare the calculated dimensions with the layout you expect. If a source is supposed to return 20 rows and 1 column but the calculation indicates 1 row and 20 columns, the total is still 20, yet the orientation may break a later formula. Dimension checks are therefore useful even when a simple total count is not enough.

Frequently Asked Questions

What formula counts the total number of elements in an Excel array?
For a rectangular array, multiply ROWS(array) by COLUMNS(array). For example, a four-row by three-column range contains 12 positions.

Does Excel have an ARRAYLENGTH function?
No general worksheet function named ARRAYLENGTH returns an array’s element count. Use ROWS and COLUMNS to measure its dimensions, then multiply them for a total.

Does COUNTA give the length of an array?
Not necessarily. COUNTA counts nonempty entries, whereas array length usually means the number of positions. A range can contain blank positions, so its dimensions can be larger than its COUNTA result.

How do I count a spilled array in Excel?
Use the spill reference for the formula’s anchor cell, such as A1#, and measure its dimensions with ROWS and COLUMNS. Multiply the two results to get the total positions.

Is LEN the same as array length?
No. LEN counts characters in text, while ROWS and COLUMNS describe array dimensions. Use LEN for a string and the dimension functions for an array.

Why does COUNTA return a different number from my array-size formula?
The size formula includes every position in the rectangle, including blanks. COUNTA counts entries Excel treats as nonempty, so empty cells and formulas returning empty text can produce a different result.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.