Browse

AREAS Function (LibreOffice Calc)

Lookup & Reference Beginner LibreOffice Calc LibreOffice 3.0

The AREAS function returns the number of ranges (areas) in a reference. It is primarily used with multi-area references created using the union operator.

The **AREAS** function returns the **number of ranges** (also called *areas*) in a reference. It is most useful when working with multi-range references created using the union operator or functions that return multiple disjoint ranges.

What the AREAS Function Does

  • Counts the number of separate ranges in a reference
  • Works with union references (e.g., A1:A3;C1:C3)
  • Useful for dynamic formulas, automation, and multi-range processing
  • Often paired with INDEX, INDIRECT, and OFFSET

Syntax

AREAS(reference)

Arguments

  • reference:
    A single range or a union of multiple ranges.

Basic Examples

Count a single range

=AREAS(A1:A10)
→ 1

Count multiple ranges (union)

=AREAS((A1:A10; C1:C10))
→ 2

Using named ranges

=AREAS(MyRange)
→ 1

Using a cell reference that contains a reference string

=AREAS(INDIRECT(A1))

Advanced Examples

Count areas in a dynamic union

=AREAS((A1:A5; OFFSET(C1;0;0;A1)))

Use AREAS with INDEX to extract each area

=INDEX((A1:A3;C1:C3); ROW(A1); 1; 1)

Loop through areas (array formula)

=AREAS((A1:A3; C1:C3; E1:E3))

Count areas returned by FILTER (if disjoint)

=AREAS(FILTER(A1:A20; A1:A20>0))

Validate that a reference contains only one area

=AREAS(A1:A10)=1

Edge Cases and Behavior Details

AREAS returns a numeric value (integer)

Accepts:

  • Single ranges
  • Multi-area ranges
  • Named ranges
  • INDIRECT references

Invalid input → Err:502

Behavior details

  • Union operator in Calc is semicolon (;)
  • AREAS does not count rows or columns — only disjoint ranges
  • A reference must be enclosed in parentheses when using unions
  • INDIRECT can be used to build multi-area references dynamically

AREAS of an error → error propagates

Common Errors and Fixes

Err:502 — Invalid argument

Cause:

  • Reference not enclosed in parentheses
  • Text not convertible to a reference
  • Incorrect union syntax

Fix:

  • Use parentheses: (A1:A3;C1:C3)
  • Wrap text references with INDIRECT

Unexpected result

Cause:

  • Misunderstanding union vs. intersection
  • Using comma instead of semicolon

Fix:

  • Use semicolon for union in Calc
  • Validate reference structure

Best Practices

  • Always wrap multi-area references in parentheses
  • Use AREAS to validate dynamic references before processing
  • Combine with INDEX to iterate through each area
  • Use INDIRECT to construct multi-area references programmatically
AREAS is your multi-range inspector — essential for automation, dynamic referencing, and advanced lookup workflows.
  • Use INDEX to extract values from each area
  • Use INDIRECT to build references dynamically
  • Use OFFSET for programmatic range construction
  • Use CHOOSECOLS and CHOOSEROWS for structured extraction

By mastering AREAS and its companion functions, you can build powerful, dynamic, and fully automated reference systems in LibreOffice Calc.