AREAS
Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell. Useful when working with multi-area references built with the union operator.
AREAS(reference)Arguments
referenceA reference to a cell or range of cells. Can refer to multiple areas using parentheses and the union operator.
=AREAS(A1)A single cell counts as one area
=AREAS(A1:A10)A contiguous range counts as one area
=AREAS((A1:A10,C1:C10,E1:E10))Three separate ranges joined by commas (union operator) count as 3 areas
- •Multi-area references must be enclosed in parentheses: (A1:A5,C1:C5)
- •The union operator is a comma in English-region Excel and varies by locale
- •Most useful in advanced formulas using INDEX with multiple areas
- •Returns 1 for any single contiguous range
- •Forgetting that a multi-area reference must be wrapped in an extra set of parentheses - without them, Excel interprets the comma as separating multiple function arguments instead of joining ranges into one reference
- •Expecting AREAS to count individual cells - it counts distinct contiguous ranges (areas), not the number of cells within them
- •Using AREAS on a reference that only looks like it might span multiple regions, like a single large range, and being confused when it returns 1 instead of some larger number
INDEXCan operate on multi-area references, with an optional area_num argument that selects which area AREAS would be counting.ROWSCounts rows within a single range, a different kind of measurement than AREAS' count of distinct regions.COLUMNSCounts columns within a single range, unrelated to AREAS' focus on the number of separate contiguous blocks.Why does AREAS return 1 when I expected multiple areas?
A single contiguous range, no matter how large, always counts as one area. You need genuinely separate, non-adjacent ranges joined with commas to get a count above 1.
Why do I get a formula error when trying to reference multiple areas?
Multi-area references need an extra set of parentheses around the whole reference, like (A1:A5,C1:C5) - without them, Excel treats the comma as separating function arguments rather than combining ranges.
Does AREAS count individual cells or ranges?
It counts distinct contiguous ranges (areas), not individual cells - a single 100-cell range still counts as just 1 area.
Need to translate a formula using AREAS?
Use our translator to convert your complete formula