AREAS

The AREAS function in Excel is used to count the number of areas in a reference. It is commonly used in conjunction with other functions and formulas to calculate the total number of non-contiguous ranges in a selected reference or range.

Syntax

=AREAS(reference)

reference The cell reference or range for which you want to count the number of separate areas or non-contiguous ranges.

About AREAS

When you need to determine the total number of distinct areas within a specified reference or range in Excel, the AREAS function comes to the rescue. It facilitates the swift and accurate computation of non-contiguous ranges, offering a convenient solution for handling complex data organization scenarios or when working with multiple disjointed sections within a spreadsheet layout. By incorporating the AREAS function into your formulas, you gain the ability to efficiently manage and analyze diverse sets of data across various locations within your worksheet. This functionality proves particularly useful for summarizing and comprehending the composition of non-contiguous data segments, contributing to improved insights and decision-making in data analysis and reporting tasks.

Examples

Suppose you have two non-contiguous ranges in your worksheet: A1:A5 and C1:C5. Using the AREAS function, you can calculate the total number of non-contiguous ranges in these references. The formula would be:

=AREAS(A1:A5,C1:C5)

This will return the count of areas, which should be 2 in this case.

Questions

Can the AREAS function be used to count non-contiguous ranges across different worksheets in a workbook?

Yes, the AREAS function can be used to count non-contiguous ranges across different worksheets within the same workbook. Simply provide the references for the non-contiguous ranges from different worksheets as arguments in the function to obtain the total count of separate areas.

Is the result of the AREAS function affected by the number of empty cells within the non-contiguous ranges?

No, the result of the AREAS function is not affected by the presence of empty cells within the non-contiguous ranges. It solely counts the distinct non-contiguous ranges within the specified reference, irrespective of the content or presence of empty cells.

Related functions

COUNTA
COUNTIF
COUNTBLANK
SUM