AREAS function

The AREAS function in Excel is used to return the number of areas in a given reference. An “area” refers to a contiguous range of cells. If a reference includes multiple disjointed ranges, each separate range counts as one area. This function is especially useful when dealing with ranges that are not contiguous, meaning there are gaps between the selected areas.

Syntax:

=AREAS(reference)

• reference: The reference to a range or multiple ranges (areas) that you want to count.

Example 1: Single Area

If you have a reference to a single range, such as A1:A5, the number of areas is 1:

=AREAS(A1:A5)

This will return 1, since it’s a single, contiguous range.

Example 2: Multiple Areas

If you have a reference to multiple disjointed ranges, such as A1:A5 and C1:C5, the number of areas is 2:

=AREAS(A1:A5, C1:C5)

This will return 2, since there are two separate areas in the reference (A1:A5 and C1:C5).

Example 3: More Complex Multiple Areas

If you refer to a range like A1:A5, C1:C5, and E1:E5, the AREAS function will return 3, because there are three disjointed areas:

=AREAS(A1:A5, C1:C5, E1:E5)

This will return 3.

Example 4: Reference with Blank Areas

If the reference includes empty cells or ranges, it still counts as an area if it is part of the same reference. For example:

=AREAS(A1:A5, A7:A10)

This will return 2, since A1:A5 and A7:A10 are separate areas (even though there’s an empty space between them).

Use Cases:

• Count disjointed ranges: It’s helpful when working with non-contiguous ranges to count how many areas are being referenced.
• Dynamic range references: If your ranges are dynamic and could include multiple areas, the AREAS function can be used to count and handle them programmatically in your formulas.

Benefits:

• Handling non-contiguous ranges: The function is valuable when you work with multiple, non-contiguous ranges, allowing you to count how many separate blocks are being referenced.
• Simplicity: The function is simple to use and helps in situations where you need to know the structure of your data ranges.