AREAS Function
Counts how many separate areas (blocks of cells) are in a reference.
Category
Lookup & Reference
Available in
All versions, Microsoft 365
Returns
A number, e.g. 3
Overview
An area is a single block of cells — one cell like A1, or a rectangle like B2:D10. Most formulas point at one area, but Excel lets a reference include several blocks at once. AREAS simply tells you how many blocks a reference contains.
On its own it's a niche function, but it's useful when building flexible formulas with INDEX, named ranges, or reports where the number of blocks can change.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| reference | Yes | A cell, a range, a named range, or several ranges wrapped in an extra set of brackets and separated by commas. |
Examples
Example 1 — The basics
Each formula below returns how many blocks are inside its reference.
| A | B | |
|---|---|---|
| 1 | Formula | Result |
| 2 | =AREAS(A1) | 1 |
| 3 | =AREAS(B2:D10) | 1 |
| 4 | =AREAS((A1:A5, C1:C5)) | 2 |
| 5 | =AREAS((A1, C3:D4, F1:F9)) | 3 |
Example 2 — Counting areas in a named range
Say you've created a name called Regions that points to three separate tables on your sheet: A2:B6, D2:E6 and G2:H6.
Result: 3. If you later add a fourth table to the name, the result updates to 4 automatically.
Example 3 — Pairing with INDEX
INDEX has an optional fourth argument, area_num, that picks which block to look in. AREAS tells you how many blocks there are, so you can check a choice is valid — or always grab the last one:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | First value in last block | ||
| 2 | 120 | 310 | 310 | ||
| 3 | 95 | 280 | |||
| 4 | 140 | 305 |
- AREAS((A2:A4, C2:C4)) returns 2 — two blocks.
- INDEX(…, 1, 1, 2) looks in block 2, row 1, column 1.
- That's C2, so the result is 310.
Common mistakes
- Missing the extra brackets. =AREAS(A1:A5, C1:C5) gives a "too many arguments" error. Use =AREAS((A1:A5, C1:C5)).
- Passing a value instead of a reference. =AREAS("A1:B2") returns #VALUE! because text isn't a reference. Wrap it in INDIRECT if you must build it as text.
- Expecting a cell count. AREAS counts blocks, not cells. Use ROWS, COLUMNS or COUNTA to count cells.
Notes
- The space (intersection) operator can shrink areas: =AREAS(B2:D4 C1:C5) returns 1, because the overlap is one block.
- All areas in a multi-area reference must be on the same sheet.
- Related functions: INDEX, ROWS, COLUMNS, INDIRECT.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.