AREAS in Excel: Count Separate Reference Blocks
Count the separate areas in an Excel reference with AREAS, understand union parentheses, and distinguish blocks from rows, cells and values.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: AREAS counts the separate reference blocks supplied to it. One rectangular range is one area, even if it contains many rows, columns or empty cells.
Availability and version history
- Introduced: Original introduction release not established by the official history reviewed.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official AREAS reference. Documentation check; examples independently calculated, not native-tested.
Syntax and why extra parentheses matter
=AREAS(reference)
AREAS has one required reference argument. To provide several separate references as a single union, enclose the comma-separated references in an extra pair of parentheses. Otherwise Excel can interpret the commas as function-argument separators. Official AREAS reference
The examples use English-language A1 formulas. A union of worksheet references is a reference expression, not a text string and not an array of the cells’ values.
Set up two distinct blocks
Use B2:C4 for a two-column block with these numeric values:
1 2
3 4
5 6
Put 10 in F2 and 20 in F3. The first block contains six cells; the second contains two. Keep the gap between them so their separate locations are obvious.
The values help compare reference structure with arithmetic later, but AREAS itself does not need those cells to contain numbers.
Example 1: a rectangle is one area
=AREAS(B2:C4)
Expected result: 1. The range is one continuous rectangle. Its three rows, two columns and six cells do not make it six separate areas.
A single cell is also one area:
=AREAS(F2)
Expected result: 1. The distinction is between separate reference blocks, not between large and small ranges.
Example 2: count two blocks in a union
=AREAS((B2:C4,F2:F3))
Expected result: 2. The inner parentheses group the two blocks into the one reference argument accepted by AREAS. The outer parentheses belong to the function call.
Compare the shape-based result with:
=SUM(B2:C4,F2:F3)
Expected sum: 51, from 1+2+3+4+5+6+10+20. SUM accepts multiple arguments directly; its argument structure is different from AREAS’s single union reference. Similar-looking punctuation does not imply the same function signature.
Example 3: add a separate cell
=AREAS((B2:C4,F2:F3,H5))
Expected result: 3. H5 is a third separate area even if it is empty. Filling H5 with 99, clearing it again, or changing the values in the first block does not change the number of reference blocks in this expression.
This makes AREAS useful for checking a selection’s structure. It does not establish that the selected cells contain valid input data.
Advanced example: use a named multi-area reference
In Excel’s name-management workflow, define a workbook name such as InputBlocks referring to the two blocks B2:C4 and F2:F3 on your actual worksheet. Use fully qualified sheet references in the definition so the intended sheet is explicit.
Then enter:
=AREAS(InputBlocks)
Expected result: 2, provided the defined name really resolves to those two separate reference areas. A named formula that returns a value array is not automatically the same thing as a named multi-area reference.
For a cautious workbook check:
=IF(AREAS(InputBlocks)=2,"Two blocks","Check selection")
Expected result: Two blocks for that definition. This confirms the area count, but not the exact addresses; review the name’s definition when location matters.
Intersection is a different reference operator
In Excel reference syntax, a space can represent intersection. The expression below asks for the overlap of two rectangles:
=AREAS(B2:D4 C3:E5)
The overlapping rectangle is C3:D4, so the expected area count is 1. This example is not a union, and the space is not an accidental separator. Avoid applying it to nonoverlapping inputs without checking the resulting reference error.
Troubleshooting
If a multi-block expression fails, inspect the inner grouping parentheses first. If the result is one when you expected several, check whether you actually supplied one larger rectangle spanning the blocks and their gap.
AREAS is not a visible-cell counter or a data-validation function. Do not use a returned count of two as proof that two groups contain equal numbers of observations. Test each range’s dimensions and contents separately when the calculation needs that guarantee.
Verification: These are fictional inputs and independently calculated expected results, not native Excel observations. See the editorial testing standards.