AREAS Function (LibreOffice Calc)

• 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

AREAS(reference)

Arguments

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

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))

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

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

• 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

• 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.