AREAS

What it does

AREAS(reference)

AREAS counts the rectangles in a reference. A reference in the Excel value model is not always one block: the union operator (the comma, in the default English locale) joins several disjoint rectangles into a single multi-area reference, and AREAS reports how many rectangles that union contains.

The count is structural, not geometric. AREAS does not merge rectangles that happen to be adjacent, and it does not deduplicate rectangles that overlap — it reports how the reference was built. Two rectangles that together tile a larger rectangle still count as two.

A single cell and a single rectangular range are both one area, so the answer is at least 1 for any reference. The interesting inputs are all unions.

Arguments

The one thing every reader gets wrong once: a union written inline needs a second set of parentheses. AREAS(A1:C4,E5) is not a one-argument call with a union in it; it parses as two arguments, which is outside the function's arity of exactly 1. The union must be parenthesized into a single argument — AREAS((A1:C4,E5)) — so that the comma is read by the reference algebra rather than by the argument list. Microsoft's page makes the same point in its examples. This is a formula-grammar collision, not a quirk of AREAS: it applies to any function that takes a reference in a position where a comma would otherwise separate arguments.

Try it

Quirks

Result and edge cases

Return kind: Number — a positive integer count.

Boundary cases specific to this function, none of which the Handbook has established for Excel:

• A whole-column or whole-row reference (A:A, 3:3) — one area, presumably, but the answer is worth recording rather than assuming.
• A three-dimensional reference (Sheet1:Sheet3!A1) — does the sheet span multiply the count, or is it one area spanning sheets? The value universe lists three-dimensional as its own reference shape, which is precisely why the answer is not derivable from the other shapes.
• A spill anchor (B1#) — a reference shape that postdates the function by decades.
• A structured reference (Table1[Amount]), including the multi-column and non-contiguous-column forms, which can designate more than one rectangle.
• Overlapping and adjacent unions, which test whether the count is structural (as stated above) or normalized.

Errors

Microsoft's page documents the argument as a reference to a cell or range of cells that can refer to multiple areas. It does not publish an error table. The expected failure is the non-reference argument — AREAS(5), AREAS("A1:B2") — for which #VALUE! is the conventional code in this family; the Handbook records that as expected, not as verified. An error value supplied as the argument propagates under the universal rule in coercion and lifting.

Note that text that looks like a reference is not a reference. AREAS("A1:B2") asks the function to count areas in a string. If you have address text, INDIRECT is the conversion step, and it brings its own consequences.

How to implement

Notes for implementers

• Reference structure must be preserved end to end. Any implementation that normalizes a reference — sorting, merging adjacent rectangles, deduplicating overlaps — before AREAS sees it will produce a different, and probably wrong, count. This is the standard trap for engines that canonicalize references early for dependency tracking.
• The comma is context-sensitive in the formula grammar: argument separator at one level, union operator inside a parenthesized reference expression. The argument separator is locale-dependent (semicolon in many locales) while the union operator's spelling is tied to it. A formula parser has to get the interaction right or AREAS((A1,B1)) will not survive a locale round trip. Formula grammar is out of the Handbook's declared scope, but the collision lands on this function's doorstep.
• The function is classified fec_dependency_profile: RefOnly — it needs the reference facility but nothing else about the workbook: no cell values are read, so no dereference of the areas is required to answer.

Rust code

eager(
    AREAS,
    "AREAS",
    CallableArityShape::exact(1),
    &[Parameter::value_or_reference("reference")],
    areas,
)

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• ROWS and COLUMNS measure a reference's extent; AREAS measures its cardinality. The three together describe a reference's shape.
• INDEX's reference form is the main consumer of multi-area references: its fourth argument, area_num, selects which area to index into. AREAS is how you find out how many there are to choose from. The pairing INDEX(ref, r, c, AREAS(ref)) — take the last area — is the idiom that makes AREAS more than a curiosity.
• The union operator (FUNC.OP_UNION_REF in the Handbook's operator catalog) is what creates the structure AREAS reports. The intersection operator can also produce references, and whether an intersection result ever has more than one area is an open question here.
• OFFSET and INDIRECT produce references at evaluation time; whether a reference so produced can carry multiple areas is a further open question.

Named variants

No named variants are recorded yet.