Contents
Yesterday we started the marathon 30 Excel functions in 30 days, starting with the function EXACT (EXACT), which turned out to be a very interesting and useful feature. We looked at several examples of its use in Excel spreadsheets.
Today we will study the function AREAS (AREAS), which is a little lighter in terms of its usefulness (to put it mildly). She’s like the lazy brother-in-law in a bad comedy, lying on your couch drinking your beer.
But even a slacker must have a purpose. This lazy brother-in-law can serve as an example for your children how NOT to behave. As for the function AREAS (AREAS), with its help we can see how the reference operators work in Excel and how they affect the results of formulas.
So, let’s see information and examples on how to use the function AREAS (AREAS) in Excel. If you have other examples, please share them in the comments. Just don’t send me your brother-in-law!
Function 02: AREAS
Function AREAS (AREAS) returns the number of regions in the link. A region can be a range of adjacent cells or a single cell. Cells can be empty or contain data – it doesn’t matter for counting.
How can the AREAS function be used?
Function AREAS (AREAS) doesn’t have many practical uses, but it’s an interesting example of how Excel’s reference operators work. You can use the function AREAS (AREAS) that’s what:
- Counting the number of areas in a range.
- Counting the number of intersections of multiple ranges.
- Calculating the number of some area for a function INDEX (INDEX).
Syntax AREAS
Function syntax AREAS (AREAS) is:
AREAS(reference)
ОБЛАСТИ(ссылка)
- A link can be a single cell, a range, or multiple areas.
Reference Operators
When writing a link, you can use any of the three link operators:
| : | Colon | A1: B4 | range | all cells between two specified cells, including the specified ones |
| , | Comma | A1,B2 | an association | concatenates multiple links together |
| ; | Semicolon () | A1;B2 | ||
| Space | A1 B3 | intersection | cells common to the specified links |
Traps AREAS
If you use a comma (or semicolon) in a function AREAS (AREAS), to enter multiple ranges of cells, you need to add another pair of brackets. More or less like this:
=AREAS((F2,G2:H2))
=ОБЛАСТИ((F2;G2:H2))
Otherwise, the comma (semicolon) will be interpreted by the program as a function field separator, and you will receive an error message.
Example 1: Counting the number of areas in a range
If you use the function AREAS (AREAS) with a simple range reference, the result is 1.
=AREAS(G2:H2)
=ОБЛАСТИ(G2:H2)
Example 2: Counting the number of areas in multiple links
You can use the function AREAS (AREAS) with multiple links to count the total number of areas. Since the comma (semicolon) is used in Excel as a concatenation operator, you will have to add a pair of parentheses to the entire expression in the formula.
=AREAS((F2,G2:H2))
=ОБЛАСТИ((F2;G2:H2))
Even if the referenced areas are adjacent, they are still counted as different, so the result of the formula is 2.
Example 3: Counting the number of areas in overlay links
Even if the areas in the links match or one of them completely includes the other, they will be counted separately when you use the union operator.
=AREAS((F2,F2:H2))
=ОБЛАСТИ((F2;F2:H2))
The F2 area is completely within the F2:H2 range, but they are counted as separate, and the result of the formula is 2.
Example 4: Counting areas in cross links
If you use space as the intersection operator, only the number of intersecting areas will be counted.
=AREAS(TESTREF01 TESTREF02)
=ОБЛАСТИ(TESTREF01 TESTREF02)
Named Range TEST REF01 painted blue and TEST REF02 – pink. These ranges intersect at three thick border points, so the result of the formula is 3.
Example 5: Calculating the area number for the INDEX function (INDEX)
Function INDEX (INDEX) in reference form can use the area number as the last argument.
INDEX(reference,row_num,column_num,area_num)
ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)
In the following example TestBlock is a non-contiguous named range consisting of 4 scopes. Name in the formula TestBlock is a link, and the function AREAS (AREAS) counts the number of areas in this range.
To get a value from a range TestBlock, which is in the 5th line of the last area, use this formula:
=INDEX(TestBlock,5,1,AREAS(TestBlock))
=ИНДЕКС(TestBlock;5;1;ОБЛАСТИ(TestBlock))
The last area is Day04, and the fifth value in Day04 – Is H05. This is the value returned by our formula.
