AREAS function

PRO

The AREAS function in Jspreadsheet Formulas Pro is a handy tool that helps you find out the count of distinct ranges within a given reference. An "area" in this context is a contiguous block of cells or a single cell within a specified reference. For instance, if you have selected multiple non-adjacent cells or ranges, the AREAS function will return the number of these separate selections. This function is particularly useful when you're working with complex data sets and need to quickly identify the number of distinct selections within your data.

Documentation

Returns the number of areas in a reference.

Syntax

AREAS(reference)

Parameter	Description
`reference`	A reference to the range for which to count the areas.

Behavior

The AREAS function is used to return the number of areas in a reference. An area is a range of contiguous cells or a single cell. Here's how it handles different cell contents:

Empty cells: AREAS considers empty cells as part of the area if they are within the defined range.
Text: AREAS doesn't differentiate between cell types, so cells containing text are considered part of the area.
Booleans: Similar to text, boolean values don't affect the function's output as it considers any cell within the specified range.
Errors: If the referenced range contains error values, it won't affect the output of AREAS function since it only counts the number of areas. However, if the referenced range itself is invalid, the function will return an error.

Common Errors

Error	Description
#VALUE!	This error occurs when the formula includes an incorrect data type.
#REF!	This error is displayed when the formula contains an invalid cell reference.

Best Practices

Always ensure that the range you are referencing in your AREAS function is valid to avoid #REF! errors.

Remember that the AREAS function counts the number of areas, not the number of cells. If you want to count the number of cells, consider using the COUNTA function.

Use absolute cell references in your AREAS function if your range will not change when the function is copied or moved.

Keep in mind that AREAS will include all cells in the range, regardless of content (text, numbers, booleans, errors, or blank cells).

Usage

A few examples using the AREAS function.

AREAS(A1:A10) returns 1  
AREAS(A1:C3, E1:F3) returns 2  
AREAS(A1:C3, A5:C7, F1:H3) returns 3

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTAzYjcyMWU2NDA2NjU4MmMwMzdlYTk3YjJjYjZhODA4OTY4ZjY1NThlZDE3ZTNmMjg5OThjYzdkYmRlYzYxODM3MWZlZGEwNDJhOTNhOTViNTM0NWQ0YWQwYzAzYjg5NzExMmQ0ZDA1NTc1ZTFkNTU1OTJhMzI0YThjZTM3MDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sales Q1",
        "Sales Q2",
        "Sales Q3",
        "Sales Q4",
        "Areas Count"
    ],
    [
        1000,
        1200,
        "",
        1400,
        "=AREAS(A2:B2)"
    ],
    [
        1500,
        1300,
        "",
        1600,
        "=AREAS(A3:B3,D3)"
    ],
    [
        800,
        900,
        "",
        1100,
        "=AREAS(A4:D4)"
    ],
    [
        "",
        "",
        "",
        "",
        "=AREAS(A2:B4,D2:D4,A1:E1)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('YTAzYjcyMWU2NDA2NjU4MmMwMzdlYTk3YjJjYjZhODA4OTY4ZjY1NThlZDE3ZTNmMjg5OThjYzdkYmRlYzYxODM3MWZlZGEwNDJhOTNhOTViNTM0NWQ0YWQwYzAzYjg5NzExMmQ0ZDA1NTc1ZTFkNTU1OTJhMzI0YThjZTM3MDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Sales Q1",
        "Sales Q2",
        "Sales Q3",
        "Sales Q4",
        "Areas Count"
    ],
    [
        1000,
        1200,
        "",
        1400,
        "=AREAS(A2:B2)"
    ],
    [
        1500,
        1300,
        "",
        1600,
        "=AREAS(A3:B3,D3)"
    ],
    [
        800,
        900,
        "",
        1100,
        "=AREAS(A4:D4)"
    ],
    [
        "",
        "",
        "",
        "",
        "=AREAS(A2:B4,D2:D4,A1:E1)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('YTAzYjcyMWU2NDA2NjU4MmMwMzdlYTk3YjJjYjZhODA4OTY4ZjY1NThlZDE3ZTNmMjg5OThjYzdkYmRlYzYxODM3MWZlZGEwNDJhOTNhOTViNTM0NWQ0YWQwYzAzYjg5NzExMmQ0ZDA1NTc1ZTFkNTU1OTJhMzI0YThjZTM3MDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sales Q1",
        "Sales Q2",
        "Sales Q3",
        "Sales Q4",
        "Areas Count"
    ],
    [
        1000,
        1200,
        "",
        1400,
        "=AREAS(A2:B2)"
    ],
    [
        1500,
        1300,
        "",
        1600,
        "=AREAS(A3:B3,D3)"
    ],
    [
        800,
        900,
        "",
        1100,
        "=AREAS(A4:D4)"
    ],
    [
        "",
        "",
        "",
        "",
        "=AREAS(A2:B4,D2:D4,A1:E1)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTAzYjcyMWU2NDA2NjU4MmMwMzdlYTk3YjJjYjZhODA4OTY4ZjY1NThlZDE3ZTNmMjg5OThjYzdkYmRlYzYxODM3MWZlZGEwNDJhOTNhOTViNTM0NWQ0YWQwYzAzYjg5NzExMmQ0ZDA1NTc1ZTFkNTU1OTJhMzI0YThjZTM3MDMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Sales Q1",
        "Sales Q2",
        "Sales Q3",
        "Sales Q4",
        "Areas Count"
    ],
    [
        1000,
        1200,
        "",
        1400,
        "=AREAS(A2:B2)"
    ],
    [
        1500,
        1300,
        "",
        1600,
        "=AREAS(A3:B3,D3)"
    ],
    [
        800,
        900,
        "",
        1100,
        "=AREAS(A4:D4)"
    ],
    [
        "",
        "",
        "",
        "",
        "=AREAS(A2:B4,D2:D4,A1:E1)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub