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

Want to practice AREAS in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=AREAS(reference) Explain

Arguments

ArgumentRequired?What it does
referenceYesA 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.

AB
1FormulaResult
2=AREAS(A1)1
3=AREAS(B2:D10)1
4=AREAS((A1:A5, C1:C5))2
5=AREAS((A1, C3:D4, F1:F9))3
A single range — however big — is still one area.
Notice the double brackets in the last two rows. Without the extra pair, Excel reads the comma as "next argument" and throws an error, because AREAS only accepts one argument.

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.

=AREAS(Regions) Explain

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:

E2fx=INDEX((A2:A4, C2:C4), 1, 1, AREAS((A2:A4, C2:C4)))
ABCDE
1Q1Q2First value in last block
2120310310
395280
4140305
=INDEX((A2:A4, C2:C4), 1, 1, AREAS((A2:A4, C2:C4))) Explain
  1. AREAS((A2:A4, C2:C4)) returns 2 — two blocks.
  2. INDEX(…, 1, 1, 2) looks in block 2, row 1, column 1.
  3. 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.
Kyle Stott

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.