Excel AREAS function

Summary

The Excel AREAS function is used to determine the number of areas in a reference. An “area” in this context is defined as a range of contiguous cells or a single cell. This function is particularly useful when dealing with multiple ranges or when you want to understand the complexity of a reference that includes multiple non-adjacent selections.
Syntax
				
					=AREAS(reference)
				
			
  • reference: The reference for which you want to count the number of areas. This can be a single area or a reference to multiple areas.
Return value
Returns a numeric value representing the count of areas in the given reference.

How to use

Enter a reference to one or more ranges in the reference argument. The function will calculate and return the number of distinct ranges included in the reference. Note that if the reference argument is omitted or is an invalid reference, the AREAS function will return the #VALUE! error.

Examples

Simple AREAS
Counting a Single Range: To count the number of areas in a single range (e.g., A1:C3):
				
					=AREAS(A1:C3)
				
			
This formula returns 1, as there is only one contiguous range.
AREAS with Multiple Ranges

Counting Multiple Ranges: To count the number of areas in a combination of multiple ranges:

				
					=AREAS((A1:C3, E1:G3))
				
			
This formula returns 2, as there are two distinct ranges.

Additional Notes

  • To reference multiple ranges in AREAS, you can use the array constant notation, where ranges are separated by commas and enclosed in parentheses.
  • The AREAS function is particularly useful in dynamic spreadsheet models where the number of areas in a reference might change based on user input or other formulas.
  • The function can also be used to validate that a range contains a specific number of areas, which can be useful in data verification or conditional formatting.

Related Functions

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel INDIRECT function

The Excel INDIRECT function converts text strings into dynamic cell references, enabling flexible and adaptable data interactions.

Content Navigation