Excel AREAS Function
The Excel AREAS function returns the number of ranges (areas) in a specified reference
Example: Excel AREAS Function
|
=AREAS(D5:D6)
|
Result in cell B5 (1) - returns the number of ranges (areas) from the selected reference.
|
|
=AREAS((D7:G9,I7:O9))
|
Result in cell B6 (2) - returns the number of ranges (areas) from the selected references.
|
|
=AREAS((D5:G7,I7:O9,Q7))
|
Result in cell B7 (3) - returns the number of ranges (areas) from the selected references.
|
|
=AREAS(Areas_Defined_Name)
|
Result in cell B8 (4) - returns the number of ranges (areas) from a defined name named Areas_Defined_Name. This defined name comprises four ranges (A1:D3,E2:K4,T4,P7:P10).
|
METHOD 2. Excel AREAS function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Lookup & Reference > AREAS > populate the input box
| =AREAS(D5:D6) Note: this example populates the Reference input box with a single range. |
 |
| =AREAS((D5:G7,I7:O9,Q7)) Note: this example populates the Reference input box with multiple (3) ranges. |
 |
| =AREAS(Areas_Defined_Name) Note: this example populates the Reference input box with a defined name that comprises multiple (4) ranges. |
 |
Sub Excel_AREAS_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("AREAS")
'apply the Excel AREAS function
ws.Range("B5") = ws.Range("D5:D6").Areas.Count
ws.Range("B6") = ws.Range("D7:G9,I7:O9").Areas.Count
ws.Range("B7") = ws.Range("D5:G7,I7:O9,Q7").Areas.Count
ws.Range("B8") = ws.Range("Areas_Defined_Name").Areas.Count
ws.Range("B5") = ws.Range("D5:D6").Areas.Count
ws.Range("B6") = ws.Range("D7:G9,I7:O9").Areas.Count
ws.Range("B7") = ws.Range("D5:G7,I7:O9,Q7").Areas.Count
ws.Range("B8") = ws.Range("Areas_Defined_Name").Areas.Count
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named AREAS.
Defined Name: Have a defined name named Areas_Defined_Name that comprises (A1:D3), (E2:K4), (T4) and (P7:P10) ranges.
Worksheet Name: Have a worksheet named AREAS.
Defined Name: Have a defined name named Areas_Defined_Name that comprises (A1:D3), (E2:K4), (T4) and (P7:P10) ranges.
ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the range references ("B5"), ("B6"), ("B7") and ("B8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel AREAS function returns the number of ranges (areas) in a specified reference.
The Excel AREAS function returns the number of ranges (areas) in a specified reference.
SYNTAX
=AREAS(reference)
=AREAS(reference)
ARGUMENTS
reference: (Required) Reference to a cell, range or a named range.
reference: (Required) Reference to a cell, range or a named range.
ADDITIONAL NOTES
Note 1: The AREAS function only automatically provides one set of brackets, however, if you are using multiple ranges you will need to insert additional brackets to the function.