AREAS function in Excel provides a convenient way to count the number of areas in a reference or array. It returns the number of ranges in a reference that contain data. This function is particularly useful when working with complex formulas or datasets spread across multiple ranges. By understanding how to use the AREAS function, users can efficiently manage and analyze their data, ensuring accuracy and consistency in their Excel workbooks. Whether you’re a beginner or an experienced Excel user, mastering the AREAS function can enhance your productivity and effectiveness in handling various data analysis tasks. With its versatility and practicality, the AREAS function proves to be an indispensable tool for Excel users seeking to streamline their workflows and optimize their data analysis processes.
This Tutorial Covers:
- What is the AREAS Function
- Syntax of AREAS Function
- Arguments of AREAS Function
- Return value of AREAS Function
- Usage notes
- Usage and Examples
- Example 1- Basic usage
- Example 2- Count the number of areas of a specific defined name range
- Things to remember in AREAS function
1. What is the AREAS Function?
A lookup/reference function in Excel is the AREAS Function. It returns an integer value that indicates the number of separate areas within a reference, which can be useful when working with complex formulas or manipulating large sets of data. The AREAS function is particularly helpful for users who need to work with multiple ranges within a worksheet and can be used in combination with other Excel functions to perform various calculations and data analysis tasks.
Starting with Microsoft Excel 2007, it is available.
-
Syntax of AREAS Function:
The syntax for the AREAS function is as follows:
=AREAS(reference)
-
Arguments of AREAS Function:
The following argument is used by the Excel AREAS function:
Reference (required argument) – This can be a named range, a normal Excel reference, or a collection of references.
-
Return value of AREAS Function:
The number of regions that make up the reference is represented by the numeric value that the regions function returns.
2. Usage notes:
The AREAS function produces a number indicating how many areas there are in the provided reference. Areas here refer to distinct, continuous ranges. AREAS only accepts the reference parameter. Reference may contain numerous references, but each reference must be separated with a comma and enclosed in its own set of parentheses. If not, Excel will interpret the commas as several arguments and will complain that you entered too many.
3. Usage and Examples:
Here are a few examples of how to apply the Excel AREAS formula.
-
Example 1- Basic usage:
A straightforward Excel function is AREAS.
Area = ranges or single cell
As a result, when we enter the formula AREAS(E1), we obtain the value 1.
Let’s now see how the function responds to ranges as a reference. The formula used is
=AREAS(D4:E8), as shown in the screenshot below.
The range that was chosen only has one value, hence this method will return 1.
In the example that follows, two ranges are chosen. The outcome of the formula, =AREAS((C1:C3,E1:E3)), is 2. We open a bracket, present ranges separated by columns, and then shut the brackets to allow the user to select more than one range.
Similar to this, selecting 3 ranges will yield a result of 3, selecting 4 ranges will yield a result of 4, and so on. Keep in mind that each range needs to be separated by a comma to avoid receiving a #NAME? error.
One of Excel’s less practical information functions is AREAS. The function only has one purpose: to return how many areas there are in a specified range of references. Practically, it is not necessary at all because Excel operations like SUM can handle universal references without requiring it. Furthermore, since we may use INDEX(Range,0,0,1) to refer to the first area of a generic range reference, it is not necessary when working with functions that can only handle single area ranges (like SUMIF).
-
Example 2- Count the number of areas of a specific defined name range:
The AREAS function can be used to count the number of ranges contained in a provided name.
Suppose you have a dataset like the one below and you want to use the AREAS formula in Excel to determine how many areas there are in “CategoryA” if the name “CategoryA” has been defined.
The steps to count the number of areas of a specific defined name range in Excel are described below:
Step 1: Apply the below formula in cell J2.
=AREAS(CategoryA)
Two areas are included in the total.
4. Things to remember in AREAS function:
- We can either type the complete formula or, after typing “=AREAS(” into a cell, use CTRL + A to open the “Insert Function Dialog Box” for more specific instructions without leaving the cell.
2. When the reference provided is in the wrong format, a #NULL! error is delivered. For instance, use AREAS(A2 D8) rather than AREAS(A2:D8).
Application of AREAS Function in Excel
- Count the number of areas: AREAS function counts the number of ranges in a reference that contain data.
- Evaluate complex formulas: It helps evaluate complex formulas with multiple ranges or arrays.
- Check data integrity: Use AREAS to verify the integrity of your data by ensuring all areas are accounted for.
- Data analysis: Incorporate AREAS into your data analysis to handle datasets spread across multiple ranges efficiently.
- Array calculations: It assists in performing array calculations involving multiple ranges or arrays.
- Formula optimization: AREAS function can aid in optimizing formulas by providing insights into how they interact with different ranges.
For ready-to-use Dashboard Templates:
