AREAS function

AREAS returns the count of distinct, non-contiguous ranges (areas) that a reference comprises, useful for evaluating complex range constructions.

=AREAS(reference)

Generate a AREAS formula

Describe what you need. The generator will reach for AREAS where AREAS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How AREAS reads its arguments
referencerequiredAREAS →
ArgumentRequiredDescription
referenceRequiredA range, named range, or array constant; if it isn’t a valid reference Excel/Sheets returns #VALUE! and empty references trigger #REF!.

Returns

A single numeric value representing how many separate areas are in the supplied reference.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count areas in a single contiguous ticket table

Ticket IDPriorityOpenedClosedAgentCSAT
101High2024-09-012024-09-02Alice9
102Low2024-09-012024-09-03Bob7
103Medium2024-09-022024-09-04Alice8
104High2024-09-03Charlie
=AREAS(A2:F5)

Result: 1

The range A2:F5 covers every column of the ticket export in one solid block, so it counts as one area. AREAS therefore returns 1, indicating no disjoint pieces are involved.

2. Count areas when selecting only IDs, priorities and CSAT columns

Ticket IDPriorityOpenedClosedAgentCSAT
101High2024-09-012024-09-02Alice9
102Low2024-09-012024-09-03Bob7
103Medium2024-09-022024-09-04Alice8
104High2024-09-03Charlie
=AREAS((A2:A5, C2:C5, F2:F5))

Result: 3

The formula builds a union of three separate column ranges: Ticket ID (A2:A5), Priority (C2:C5) and CSAT (F2:F5). Each column is its own area, so AREAS counts three distinct blocks and returns 3.

3. Count areas in a mixed-range that skips the "Closed" column

Ticket IDPriorityOpenedClosedAgentCSAT
101High2024-09-012024-09-02Alice9
102Low2024-09-012024-09-03Bob7
103Medium2024-09-022024-09-04Alice8
104High2024-09-03Charlie
=AREAS((A2:B5, D2:E5))

Result: 2

Here we join two blocks: columns A-B (Ticket ID and Priority) and columns D-E (Closed and Agent). Because the "Opened" and "CSAT" columns are omitted, the reference consists of two separate areas, so AREAS returns 2.

Common errors

Which AREAS error are you seeing?
AREAS returned an error#VALUE!
Replace the literal with a proper range like A2:F5 or a named range that points to the ticket table.
#REF!
Adjust the formula to reference existing cells, or recreate the named range so it points to a valid area.
#SPILL!
Clear the destination cells or move the formula to a location where the spill can expand without obstruction.
ErrorWhy it happensHow to fix it
#VALUE!The argument supplied is not a range or reference—e.g., a plain number or text string.Replace the literal with a proper range like A2:F5 or a named range that points to the ticket table.
#REF!The reference points to cells that no longer exist, such as after deleting rows or columns that were part of the original range.Adjust the formula to reference existing cells, or recreate the named range so it points to a valid area.
#SPILL!In Google Sheets, using a dynamic array that spills into occupied cells while AREAS tries to evaluate the whole array.Clear the destination cells or move the formula to a location where the spill can expand without obstruction.

Tips and when to use something else

  • Use AREAS when you need to verify that a constructed range is truly contiguous before applying functions that require a single block.
  • Combine AREAS with COUNTA to ensure you’re counting rows only after confirming there is exactly one area.
  • If you only need the number of rows or columns, prefer ROWS or COLUMNS—these give size without checking contiguity.
  • When you need to extract data from a specific area of a non-contiguous range, consider using INDEX or FILTER instead of AREAS.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.ROWSROWS returns the number of rows in a given array or range, counting every row including headers and blank rows within the reference.

Frequently asked questions

Why does AREAS return 1 for a whole table even though the table has many columns?
AREAS counts distinct *areas*, not individual cells or columns. A rectangular block that spans multiple columns and rows is still a single contiguous area, so the function returns 1.
Can AREAS be used on a named range that refers to several non-adjacent cells?
Yes. If the named range is defined as a union of separate ranges, AREAS will return the count of those separate pieces. For example, a name that equals A2:A5, C2:C5 will yield 2.
What is the difference between AREAS and COUNTA?
COUNTA tallies non-blank cells, while AREAS counts how many separate blocks of cells are referenced. You can have a large COUNTA result with only one area, or a small COUNTA result spread across many areas.
Is AREAS available in Google Sheets, and does it behave the same as Excel?
Google Sheets supports AREAS with the same basic behavior—returning the number of distinct ranges. However, Sheets may produce a #SPILL! error when the argument is a dynamic array that cannot expand, a nuance not present in Excel.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator