Part of the free Module 5: Excel Formulas and Functions · Function 4 of 105 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 (and earlier).
The Excel AREAS function returns the number of separate areas in a reference. An area is one contiguous block of cells or a single cell, so AREAS(A1:B5) returns 1, while AREAS((A1:A5, C1:C5)) returns 2. Use AREAS to test multi-area named ranges, to validate a selection before a macro or an INDEX formula processes it, and to document unusual references for reviewers.
AREAS syntax
=AREAS(reference)| Argument | Required or optional | What it does | Notes and defaults |
|---|---|---|---|
| reference | Required | The cell, range, named range or union of ranges whose areas you want to count. | To pass more than one range, wrap the whole comma-separated list in an extra pair of parentheses, for example AREAS((A1:A5, C1:C5)). Without the inner parentheses Excel reads each range as a separate argument and reports too many arguments. |
How AREAS works
AREAS counts blocks, not cells. It inspects the reference you give it and returns a positive whole number equal to the number of contiguous rectangles inside it. A normal range is always one area, however large. A union reference, which is what Excel creates when you select several blocks with Ctrl held down or type ranges separated by commas, has one area per block. Two blocks that touch each other, such as A1:C1 and A2:C2, still count as two areas because they were supplied separately.
The function only looks at the structure of the reference. It ignores the contents of the cells entirely, so empty ranges count in exactly the same way as full ones. AREAS is not volatile and it returns a number, which makes it safe to use inside IF, INDEX and other functions.
Version notes: AREAS has behaved the same way in every version of Excel since Excel 97, including Excel 365, Excel for the web and Mac. Google Sheets does not have an AREAS function. There is no modern replacement because the dynamic-array functions such as FILTER and VSTACK work with single rectangular arrays rather than unions.
Worked examples
Example 1: Count the areas in simple and union references
These formulas show how the count changes as you add blocks. Notice the double parentheses whenever more than one range is passed.
| Formula | Result | Why |
|---|---|---|
=AREAS(A1:B2) |
1 | One rectangular block of four cells. |
=AREAS((A1:A2, B1:B2)) |
2 | Two blocks supplied separately, even though they touch. |
=AREAS((A1:B2, C1:D2, E1:F2)) |
3 | Three blocks in the union. |
=AREAS(D7) |
1 | A single cell is one area. |
The inner parentheses turn the list of ranges into a single union reference, which is the one argument AREAS expects.
Example 2: Validate a multi-area named range in a data-entry template
A template asks users to fill three separate input blocks. The named range InputCells is defined in Formulas > Name Manager as =Sheet1!$B$3:$B$6,Sheet1!$D$3:$D$6,Sheet1!$F$3:$F$6. A check cell confirms the name still covers all three blocks.
| Named range | Definition | Formula in the check cell | Result |
|---|---|---|---|
| InputCells | B3:B6, D3:D6, F3:F6 | =IF(AREAS(InputCells)=3, "OK", "Name has "&AREAS(InputCells)&" areas") |
OK |
| InputCells (after someone deleted one block) | B3:B6, D3:D6 | same formula | Name has 2 areas |
If a colleague edits the name and drops a block, the check cell flags it immediately, before a macro or a downstream formula fails.
Example 3: Pick one area with the reference form of INDEX
The reference form of INDEX has an area_num argument, which is the natural partner of AREAS. Three monthly columns hold quantities, and a cell in H1 holds the month number to total.
| B (Jan) | D (Feb) | F (Mar) | H1 | Formula | Result |
|---|---|---|---|---|---|
| 10, 20, 30 | 5, 15, 25 | 8, 12, 40 | 2 | =SUM(INDEX((B2:B4, D2:D4, F2:F4), 0, 1, H1)) |
45 |
| 10, 20, 30 | 5, 15, 25 | 8, 12, 40 | 3 | same formula | 60 |
With H1 set to 2, INDEX returns the whole of the second area (D2:D4) and SUM totals 5 + 15 + 25 = 45. Guard the formula with AREAS so an out-of-range month cannot produce #REF!:
=IF(H1>AREAS((B2:B4, D2:D4, F2:F4)), "Invalid month", SUM(INDEX((B2:B4, D2:D4, F2:F4), 0, 1, H1)))Example 4: Document a complex reference for reviewers
Combine AREAS with ROWS and COLUMNS to describe what a reference contains in plain text.
="This selection has "&AREAS((A1:A5, C1:C5, E1:E5))&" areas of "&ROWS(A1:A5)&" rows each"The result is This selection has 3 areas of 5 rows each. A one-line description like this next to a named range saves the next analyst from opening Name Manager.
Tips and common mistakes
- Double the parentheses. AREAS((A1:A2, C1:C2)) is correct; AREAS(A1:A2, C1:C2) triggers the too-many-arguments message. This is the single most common problem with the function.
- Test multi-area names before macros use them. A check cell built on AREAS catches accidental edits to a name long before a VBA loop fails.
- Do not confuse areas with cells, rows or columns. Use COUNTA for filled cells, ROWS and COLUMNS for size. AREAS only counts blocks.
- Touching blocks still count separately. Merge adjacent ranges into one rectangle if you want a count of 1.
- Prefer contiguous data. SUMIFS, FILTER, PivotTables and Excel Tables cannot read union references; AREAS is a diagnostic tool, not a reason to keep data scattered.
- Union separator depends on locale. In regional settings that use a semicolon as the list separator, the union operator is still a comma inside the inner parentheses in most locales, but check your own copy of Excel if the formula is rejected.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| Too many arguments message | Several ranges were passed without the extra parentheses. | Wrap the list: AREAS((A1:A2, B1:B2)). |
| #VALUE! | The argument is not a reference, for example text, a number or the result of a function that returns a value rather than a reference. | Pass a range, a named range or a function that returns a reference such as OFFSET or INDEX. |
| #REF! | One of the ranges in the reference was deleted after the formula was written. | Re-point the formula or redefine the named range in Name Manager. |
| #NAME? | A named range in the argument does not exist or is misspelt. | Check the name in Formulas > Name Manager. |
| Unexpected 1 | You expected a count of cells or rows. | Use COUNTA, ROWS or COLUMNS; AREAS counts blocks only. |
Practice exercise
- Enter =AREAS(B2:E10) in a cell. Expected: 1.
- Enter =AREAS((B2:E10, G2:G10)) in a cell. Expected: 2.
- Create a named range called Inputs that covers A1:A3 and C1:C3, then write a formula that returns OK when it has exactly two areas. Expected: OK.
- Using three single-column ranges of your own, write an INDEX formula with area_num driven by a cell, and guard it with AREAS so an area number that is too high shows the text Invalid instead of #REF!.
- Write a text formula that reports the number of areas and the number of rows in the first area. Expected: a sentence such as 2 areas of 3 rows.
Key takeaways
- AREAS returns how many contiguous blocks a reference contains; a single range is always 1.
- Pass several ranges as one union by wrapping them in an extra pair of parentheses.
- It counts structure only and never looks at cell contents.
- Its main practical uses are validating multi-area named ranges and guarding the area_num argument of INDEX.
- Google Sheets has no equivalent, and dynamic-array functions do not accept union references.
Related functions and lessons
- Excel Formulas and Functions hub: every function lesson in module 5.
- ROWS and COLUMNS: measure the height and width of a single area.
- INDEX: its reference form selects one area by number, the natural partner of AREAS.
- ISREF: tests whether a value is a reference at all before you count its areas.
- OFFSET: builds references that AREAS can inspect.
- Data Validation course: build the input checks that AREAS formulas often support.
- Common errors in Excel formulas and how to fix them.
- Microsoft documentation: AREAS function.
Frequently asked questions
How many arguments does the AREAS function take?
Exactly one, the reference. To count several ranges you combine them into one union reference by wrapping the comma-separated list in an additional pair of parentheses, for example AREAS((A1:A5, C1:C5)). Excel then treats the whole union as the single argument and returns 2.
Why does AREAS say I have entered too many arguments?
The ranges were separated by commas without the extra parentheses, so Excel read each range as a separate argument and AREAS accepts only one. Add the inner parentheses around the list. The same rule applies to any function that accepts a union reference, including the reference form of INDEX.
What is the difference between AREAS and COUNTA?
AREAS counts how many separate blocks a reference contains and ignores what is inside them. COUNTA counts the non-empty cells inside those blocks. For A1:A5 with three filled cells, AREAS returns 1 and COUNTA returns 3.
Does AREAS work with named ranges?
Yes. AREAS(MyRange) returns the number of blocks in the name, which is the most useful way to use the function. Define a multi-area name in Name Manager with ranges separated by commas, then a check formula built on AREAS confirms the name still covers the expected number of blocks.
Is there an AREAS function in Google Sheets?
No. Google Sheets has no AREAS function and does not support union references in the same way. If you share a workbook between Excel and Sheets, avoid multi-area names and keep data in single rectangular ranges so that formulas behave the same in both.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built with these functions are available at NextGenTemplates.com.