How to Use the AREAS Function in Excel

Introduction

The AREAS function in Excel and Google Sheets is designed to return the number of areas within a specified reference. An “area” is defined as a range of contiguous cells, including individual rows or columns. This function proves particularly useful when dealing with non-contiguous ranges, allowing users to efficiently count the separate areas contained within them.

Syntax

The syntax for the AREAS function is as follows:

AREAS(reference)

reference refers to the cell range, rows, or columns for which you wish to count the areas.

Examples

To better understand the functionality of the AREAS function in Excel and Google Sheets, let’s explore some practical examples:

Example 1: Counting Areas in Contiguous Ranges

In this example, consider the contiguous ranges A1:B3 and D1:E3. We aim to determine the number of areas within these ranges.

Range	Formula	Result
A1:B3, D1:E3	=AREAS(A1:B3,D1:E3)	2

The AREAS function accurately indicates that there are 2 separate areas in the specified ranges.

Example 2: Counting Areas in Non-Contiguous Ranges

Using the same non-contiguous ranges A1:B3 and D1:E3, we will calculate the areas.

Range	Formula	Result
A1:B3, D1:E3	=AREAS(A1:B3,D1:E3)	4

Here, the AREAS function identifies 4 separate areas, counting 2 areas within each range.

Example 3: Using Named Ranges

Named ranges, such as “Range1” and “Range2”, can also be utilized with the AREAS function.

Range	Formula	Result
Range1, Range2	=AREAS(Range1,Range2)	2

Even when using named ranges, the AREAS function efficiently tallies the number of distinct areas.

Conclusion

The AREAS function is an invaluable tool in Excel and Google Sheets for determining the number of areas within a reference, whether it includes one or multiple non-contiguous ranges. Mastering the use of this function through varied examples allows users to enhance their spreadsheet management skills substantially.

More information: https://support.microsoft.com/en-us/office/areas-function-8392ba32-7a41-43b3-96b0-3695d2ec6152

Other functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

COLUMNS

Returns the number of columns in a reference

FILTER

Filters a range of data based on criteria you define

FORMULATEXT

Returns the formula at the given reference as text

GETPIVOTDATA

Returns data stored in a PivotTable report

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

INDIRECT

Returns a reference indicated by a text value

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation

SORT

Sorts the contents of a range or array

SORTBY

Sorts the contents of a range or array based on the values in a corresponding range or array

TRANSPOSE

Returns the transpose of an array

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match

XMATCH

Returns the relative position of an item in an array or range of cells