What is the AREAS function?

The AREAS function returns the number of cell ranges and single cells in the specified argument.

How does a cell reference to a cell range look like?

A colon separates the start reference and the end reference. The start reference must be the top left cell in the cell range and subsequently the end reference must be the bottom right cell in the cell range.

B3:G7 is an example of a cell reference to a cell range.

What does a cell reference to a single cell look like?

For example, B3 is a cell reference to a single cell.

1. AREAS Function Syntax

AREAS(reference)

2. AREAS Function Arguments

reference

Required. A reference to a cell(s) or a cell range(s).

Use parentheses to include multiple arguments as a single argument, see the example below.

3. AREAS Function example

Formula in cell B3:

Use a set of parentheses to prevent interpreting the comma as a separator of arguments.

4. AREAS Function not working

Make sure to add extra parentheses if you use more than one cell reference. If you forget the parentheses Excel shows a dialog box containing the message "You have entered too many arguments for this function."

5. How to count cells in a cell reference

The formula below returns the total number of cells in the specified range.

Formula in cell B3:

=ROWS(B3:V16)*COLUMNS(B3:V16)

Explaining formula

Step 1 - Count rows in cell range

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(B3:V16)

Step 2 - Count columns in cell range

The COLUMNS function calculates the number of columns in a cell range.

Function syntax: COLUMNS(array)

COLUMNS(B3:V16)

Step 3 - Count columns in cell range

The asterisk lets you multiply numbers in an Excel formula.

ROWS(B3:V16)*COLUMNS(B3:V16)

becomes

14*21 equals 294

6. How to count cells in multiple cell references

Cells B3 to B5 contain different cell references with variable sizes. The Excel 365 formula in cell B8 counts the cells for each cell reference and returns a total.

Formula in cell B8:

Explaining formula

Step 1 - Convert text string to ref

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference.

Function syntax: INDIRECT(ref_text, [a1])

INDIRECT(a)

Step 2 - Count columns in ref

The COLUMNS function calculates the number of columns in a cell range.

Function syntax: COLUMNS(array)

COLUMNS(INDIRECT(a))

Step 3 - Count rows in ref

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(INDIRECT(a))

Step 4 - Multiply rows and columns

The asterisk lets you multiply numbers in an Excel formula.

COLUMNS(INDIRECT(a))*ROWS(INDIRECT(a))

Step 5 - Construct LAMBDA function

The BYROW function requires a LAMBDA function to work properly.

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,COLUMNS(INDIRECT(a))*ROWS(INDIRECT(a)))

Step 6 - Iterate refs row by row

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(B3:B5,LAMBDA(a,COLUMNS(INDIRECT(a))*ROWS(INDIRECT(a))))

Step 7 - Calculate a total

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM(BYROW(B3:B5,LAMBDA(a,COLUMNS(INDIRECT(a))*ROWS(INDIRECT(a)))))

becomes

SUM({23; 45; 200})

and returns 268

Back to top

Useful resources

AREAS function - Microsoft
AREAS function

Functions in 'Lookup and reference' category

The AREAS function function is one of many functions in the 'Lookup and reference' category.

Excel function categories

Excel categories

Leave a Comment