AREAS function
AREAS counts how many separate ranges are in a multi-area reference and returns that number. It is rarely used outside advanced reference work, and multiple areas need an extra set of parentheses.
Example
Read it as a sentence: count how many separate areas the reference B1:C2 contains.
Excel shows 1
B
C
1
Item
Category
2
Boxer cartridge
Tools
Items
Syntax
AREAS(reference)
-
reference(required) - A reference, or several in parentheses.
Returns: The number of separate areas in the reference.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=AREAS(Items!B1:C2) | 1 | One area |
=AREAS(Items!B1:D3 Items!C2:E4) | 1 | Intersection |
=AREAS(INDIRECT("Items!B1:C2")) | 1 | Reference from INDIRECT |
=AREAS((Items!B1:C2,Items!E1,Data!F1:F3)) | #VALUE! | Union of three |
=AREAS(Items!B1:B2 Items!D1:D2) | #NULL! | Empty intersection |
The Items sheet these formulas read
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
Data
Watch out for
- Multiple areas need an extra set of parentheses: AREAS((A1:B2,C3)).
- Does not count cells, only areas.
Where it works
- Excel
- AREAS is in every version of Excel from 2007 on.
- Google Sheets
- No direct equivalent. No Sheets counterpart for multi-area references.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .