30 Excel Functions in 30 Days: 02 – AREAS

Icon30DayYesterday we started the 30XL30D challenge with the action-packed, fun-filled, EXACT function. It had several examples and ways to apply it in your own workbooks.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

Today we’ll investigate the AREAS function, and it’s a bit lighter in the usefulness department (to put it politely). It’s like the lazy brother-in-law, in a bad sitcom, who lies on your couch, and drinks your beer.

But, even a layabout can have a purpose. That lazy brother-in-law can serve as an example for your children, of how NOT to behave. As for the AREAS function, we can use it to see how the 3 different reference operators work, and how they affect the formula results.

So, let’s take a look at the AREAS information and examples, and if you have other examples, please share them in the comments. But don’t send me your brother-in-law!

Function 02: AREAS

The AREAS function returns the number of areas in a reference — an area is a range of contiguous cells or a single cell. The cells can be empty, or contain data – that has no effect on the count.

Areas00

How Could You Use AREAS?

The AREAS function doesn’t have many real-world uses, but it’s an interesting example of how the reference operators work. You can use the AREAS function to do the following:

  • Count the number of areas in a range
  • Count the number of intersections for multiple ranges
  • Calculate an area number for an INDEX function

AREAS Syntax

The AREAS function has the following syntax:

  • AREAS(reference)
    • reference can be a single cell or range, or can refer to multiple areas.

Reference Operators

When entering references, you can use any of the 3 reference operators:

: colon A1:B4 Range all cells between, and including, the two references
, comma A1, B2 Union combine multiple references into one
space A1 B3 Intersection cells common to the references

AREAS Traps

If you are using a comma in the AREAS function, to refer to multiple ranges or cells, add another set of parentheses.

=AREAS((F2,G2:H2))

Otherwise, the comma will be interpreted as a field separator, and you’ll get a “too many arguments” error.

Areas06

Example 1: Count the Areas in a Range

You can use the AREAS function with a simple range reference, and the count will be 1.

=AREAS(G2:H2)

Areas01

Example 2: Count the Areas in Multiple References

You can use the AREAS function with multiple references, to get a total count of areas. Because a comma is used as the union operator, you’ll need to add an extra set of parentheses in the formula.

=AREAS((F2,G2:H2))

The two ranges in the reference are adjacent, but are counted as separate areas, so the formula result is 2.

Areas02

Example 3: Count the Areas in Overlapping References

Even if the references overlap, or one reference is completely within another, when using the comma as the union operator, each area will be counted separately.

=AREAS((F2,F2:H2))

The two references overlap, and F2 is completely within the F2:H2 range, but they are counted as separate areas, so the formula result is 2.

Areas03

Example 4: Count the Areas in Intersecting References

When you use the space character to create an intersection from the references, the intersection areas will be counted.

=AREAS(TESTREF01 TESTREF02)

The named range TESTREF01 is coloured blue and TESTREF02 is coloured purple. These ranges intersect at three points, outlined with bold borders, so the formula result is 3.

Areas04

Example 5: Calculate the Area Number for INDEX

The INDEX function, in Reference form, can use area number as its final argument.

INDEX(reference,row_num,column_num,area_num)

This example, based on an Excel newsgroup post by Leo Heuser, refers to a non-contiguous named range – TestBlock. In the INDEX formula, TestBlock is the reference, and the AREAS function calculates the number of areas in the TestBlock range.

To get the value from TestBlock, row 5, column 1, last area, use this formula:

=INDEX(TestBlock,5,1,AREAS(TestBlock))

The last area is Day04, and the 5th value in Day04 is H05, which is the formula result.

Areas05

Download the AREAS Function File

To see the formulas used in today’s demo, you can download the AREAS function sample workbook. The file is zipped, and is in Excel 2007 file format. Try to use each function in your own workbooks. Then, for extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you’ll remember it better.

Watch the AREAS Video

To see a demonstration of the examples in the AREAS function sample workbook, you can watch this Excel video tutorial.

YouTube link: Count Areas With Excel AREAS Function

_____________

You may also like...

15 Responses

  1. Jim Cone says:

    Hi Debra,
    Never paid any attention to the Areas function in Excel, just in VBA.
    I will now, it is a very informative writeup.
    Better check the couch however, I think that brother-in-law may have gotten to the keyboard.

    Shouldn’t…
    “The named range TESTREF01 is coloured blue and TESTREF01 is coloured purple.
    These ranges intersect at three points, outlined with bold borders, so the formula result is 4.”

    Actually be…
    The named range TESTREF01 is coloured blue and TESTREF02 *** is coloured purple.
    These ranges intersect at three points, outlined with bold borders, so the formula result is 3 ***.

  2. Thanks Jim, it’s fixed now — and I’ll have to keep a closer eye on that guy!

  3. Jon Peltier says:

    I don’t think I knew about this one. I knew of the Areas property of the Range object in VBA, and that’s very useful. I can’t think of a use for this one off the top of my head.

  4. @Jon, thanks — glad I’m not the only one who can’t see a good use for this function.

  5. Ruby says:

    Still need to change the second TESTREF01 to TESTREF02 (…is coloured purple.)

  6. Thanks Ruby, it’s fixed now.

  7. Contextures Blog » 30 Excel Functions in 30 Days: 24 - INDEX says:

    […] AREAS to find the last area in a named […]

  8. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] AREAS […]

  9. Norman Harker says:

    Hi Debra!

    I’m busy updating and upgrading my Functions Lists for 2007 – 2010 and adding a help / examples button for at least the most common functions.

    AREAS? Has anyone ever found a use for it? I think your guy on the couch drinking your beer is related to Chip Pearson’s drunken uncle DATEDIF although at least he’s useful (if a tad ynreliable).

    Regards.

  10. Jeffrey says:

    Hi Debra,

    I have experience something with the INDEX function that baffles me, and I hope you can shed me some light. FYI I am using Excel 2007.
    My question is, I have data in a table and row 1 is the header. I am using the first syntax i.e. index(array,row_num). When I use index(H2:H10,0), the formula will return the value in H2 but if I change my array to H3:H10 I will get #VALUE! which I suppose should be the correct answer.

    Regards

    • @Jeffrey, try using 1 as the row number in the INDEX function, instead of zero. That should get rid of the error.

      • Jeffrey says:

        Hi Debra,

        Thanks for your response. Actually the 0 was derived from a sumproduct formula for searching multiple criteria (now it came back to me that I could also use match function for searching with multiple criteria, thanks to this blog) and it will return 0 if the criteria was not met. I’m okay with the error because that should be the correct answer if row number is 0 but what I don’t understand and sort of confusing is why my index formula return a value if my array/range starts at row 2 just below the header row.
        Index(H2:H10,0) would not return an error but Index(H3:H10,0) would, why the inconsistency?

        Regards

  11. Jeff Weir says:

    Hi Jeffrey. This is due to the magical world of array formulas. This is tricky to explain, but I’ll give it a shot.
    http://office.microsoft.com/en-nz/excel-help/index-HP005209138.aspx tells us “If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.”
    What this DOESN’T tell you is that while such an INDEX function returns an array , for the non Array-Entered version, what actually gets displayed depends on where abouts in the spreadsheet you’ve entered this function.
    1. If the formula happens to be entered in a row/column that is parallel to the range that INDEX points at, then the INDEX function will return an entire array, but will display only the value of the corresponding row/column in the indexed range.
    2. Otherwise it returns a #VALUE error, which basically means “Hey, I’ve got a whole list of numbers/text, but I’ve only got one cell to display them in, and I don’t know which one in particular that you want me to display”.

    Normally a function that tries to returns a whole list into just one cell shows a #VALUE error, unless it has some clue in regards to which particular value you want to display from such a list. But if you click on a cell that has this error, and push F9 (which tells Excel to ‘evaluate’ the list) then you’ll see that in fact it’s not really an error value at all, but instead is a whole bunch of numbers/text.

    From your question, I believe that your formula just happens to be in row 2, and that you have NOT array-entered the function i.e. you entered the formula into the cell, and pushed ENTER (which is fine for your purposes).
    As long as your formula is Index(H2:H10,0) then your formula will return a value…in particular it will return the value from H2. But as soon as you change that formula to Index(H3::H10,0), then you’ll get the #VALUE warning.

    Here’s an example to help illustrate this.
    Say cell A1 has the text “Header” in it, and you have the values 2;3;4;5 entered into cells A2;A3;A4;A5. If you type =INDEX($A$2:$A$5,0) into cell B1 and copy it down to B6, then in cells B1 and B6 you will see a #VALUE error, because rows 1 and 6 DON’T have corresponding rows in the range that you INDEX function points to. However, cells B2 through B5 it will return the corresponding value from cells A2 through A5.

    What’s also interesting is that if you select cells B1 through B6 in turn, and highlight the entire formula in the formula bar and then press F9, you’ll see that they all return exactly the same thing:
    ={1;2;3;4;5}
    …even though they all display different things.
    John Walkenbach covers Array Formulas in his books very well, and you’ll find lots of resources on the web.

    • Jeffrey says:

      Hi Jeff,
      Thanks very much for your explanation and the example, appreciate it. I didn’t know that the location where I enter the INDEX formula could also affect the output.

      Regards

  12. Jeff Weir says:

    No sweat. Glad I could help. Sorry about all the italics in the above comment…I stuffed up the formatting.

Leave a Reply

Your email address will not be published. Required fields are marked *