The Excel AREAS function answers one tiny but surprisingly useful question: how many separate blocks of cells does this reference contain? A plain range like A1:B3 is one area; a union like A1:B3 plus D1:E2 is two. AREAS is the auditing partner of INDEX’s little-known area_num argument and a quick way to inspect multi-block named ranges.
Syntax
| Argument | Description | |
|---|---|---|
reference | Required | A reference to a cell, a range, a multi-area union joined with commas, or a named range. To pass a union, wrap it in an extra pair of parentheses: (A1:B3, D1:E2). |
Available in: every version of Excel — AREAS has been around since the earliest releases, so formulas using it open everywhere.
Count the areas in a reference
An area is one contiguous block of cells. The sheet below has two highlighted blocks — A1:B3 and D1:E2:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | North | 1,200 | West | 2,300 | |
| 2 | South | 1,750 | Central | 1,980 | |
| 3 | East | 2,040 |
The double parentheses matter. AREAS takes exactly one argument, and inside a function call Excel normally treats a comma as “next argument.” The inner parentheses tell Excel the commas are union operators, gluing several blocks into one reference.
Try it: interactive AREAS demo
Build a reference block by block, toggle the extra parentheses, and watch what AREAS returns.
Where AREAS earns its keep: INDEX and named ranges
AREAS pairs naturally with the multi-area form of INDEX, whose rarely-used 4th argument area_num picks which block to read from:
Before looping through areas with area_num, use AREAS to find out how many there are — especially when the reference is a named range someone else built:
Names with multiple areas are created by selecting blocks with Ctrl held down and then defining a name. AREAS is the quickest audit: if it returns more than 1, the name is a union, and functions like SUM will happily total all of it while others (VLOOKUP, for one) will choke.
Errors & common pitfalls
“You’ve entered too many arguments” — the missing parentheses. =AREAS(A1:B3, D1:E2) looks right but isn’t: Excel reads the comma as an argument separator and refuses the formula. Wrap the union in its own parentheses: =AREAS((A1:B3, D1:E2)).
#VALUE! — not a reference. AREAS needs an actual reference. Text like "A1:B3" or the result of most calculations is not a reference — pass the range itself, a named range, or wrap text with INDIRECT first.
#NULL! — a space where a comma should be. In references a space is the intersection operator. =AREAS((A1:B3 D1:E2)) asks for the overlap of two blocks that don’t overlap — #NULL!.
Pitfall: AREAS doesn’t count cells, rows, or non-empty regions. =AREAS(A1:Z100) is 1, full stop. For cell counts use ROWS×COLUMNS or COUNTA; for trimming blank edges in Excel 365, see TRIMRANGE.
Pitfall: spilled arrays aren’t multi-area references. A dynamic array result like E2# is still one rectangular area — AREAS returns 1, never the number of rows it spilled.
Practice workbook
Frequently asked questions
What does the AREAS function actually do?
Why does =AREAS(A1:B3, D1:E2) give a 'too many arguments' error?
=AREAS((A1:B3, D1:E2)).Can I use AREAS on a named range?
What's the difference between AREAS, ROWS, and COLUMNS?
Which Excel versions support AREAS?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class