DocumentationFormulasFunctionsGroupby

GROUPBY function

PRO

The GROUPBY function in Jspreadsheet Formulas Pro summarizes a range by grouping the rows that share the same value in one or more fields and aggregating the related values. It works like a lightweight pivot table written as a single formula: you point it to the field column, the values column and an aggregation function such as SUM or AVERAGE, and it spills a summary table with one row per group and an optional total row. Because the result is a dynamic array, the summary updates automatically whenever the source data changes.

Documentation

Groups the rows of a range by one or more fields and aggregates the associated values, returning a summary table.

Category

Lookup and reference

Syntax

GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Parameter Description
row_fields A column-oriented range or array with the values to group by. Adjacent columns create nested groups.
values A column-oriented range or array with the values to aggregate. It must have the same number of rows as row_fields. Each additional column produces one more result column.
function The aggregation applied to each group, such as SUM, AVERAGE, COUNT, MAX, MEDIAN, PERCENTOF or a LAMBDA. Use HSTACK(SUM, AVERAGE) to apply several aggregations side by side.
[field_headers] Optional. When omitted, a header row is detected automatically. 0 = the ranges have no headers, 1 = the first row holds headers but they are not shown, 2 = no headers, generate them, 3 = the first row holds headers and they are shown.
[total_depth] Optional. 0 = no totals, 1 = grand total (default), 2 = grand total and one subtotal per first-level group. Negative values (-1, -2) place the totals at the top of the result.
[sort_order] Optional. The result column to sort by, counting the row fields first and then the value columns, or an array of key columns such as {2,1}. Use a negative number for descending order.
[filter_array] Optional. A column of TRUE/FALSE values with one entry per row, for example C2:C6>4, that selects the rows to include.
[field_relationship] Optional. 0 = hierarchy (default), 1 = table. Only meaningful when more than one row field is provided.

Behavior

The GROUPBY function returns a dynamic array with one row per unique combination of the row fields, sorted in ascending order by default, followed by a Total row.

  • When field_headers is omitted, a header row in the ranges is detected automatically. Setting it to 0 explicitly treats the header text as data, which adds a group whose sum is 0.
  • With two or more row fields and total_depth equal to 2, the result contains a subtotal row for each first-level group and a final Grand Total row.
  • When several aggregations are passed with HSTACK, the result gains a header row with the function names, for example SUM and AVERAGE.
  • A LAMBDA receives the values of each group as an array, so any custom aggregation can be written, such as LAMBDA(x, MAX(x)). A LAMBDA with two parameters also receives the values of all groups, for example LAMBDA(x, y, SUM(x) / SUM(y)) for the share of each group.
  • Using PERCENTOF as the function returns the share of each group relative to the total of all groups.
  • The filter_array is applied before grouping, so the totals reflect only the rows that pass the filter.
  • The result spills into the cells below and to the right of the formula. Its size changes with the number of distinct groups.

Common Errors

Error Description
#VALUE! Returned when row_fields, values and filter_array do not have the same number of rows, when function is not a function (for example the text "SUM"), or when field_headers, total_depth or sort_order receive a value outside the accepted set.

Best practices

  • Set field_headers to 3 when the ranges include a header row and you want the labels shown in the result. Avoid setting it to 0 in that case, because the header text becomes a group of its own.
  • Keep the cells below and to the right of the formula empty, because the spilled range grows and shrinks with the number of groups.
  • Use HSTACK(SUM, AVERAGE) as the function when you need more than one aggregation of the same data, instead of separate formulas.
  • Use filter_array with a comparison such as C2:C6>4 instead of copying the filtered rows to another range.
  • Use sort_order 2 or -2 to rank the groups by their aggregated value rather than by name.

Usage

A few examples using the GROUPBY function.

// Data: A2:A6 = North, South, North, South, North
//       B2:B6 = Pen, Pen, Book, Book, Pen
//       C2:C6 = 10, 5, 2, 8, 3

GROUPBY(A2:A6, C2:C6, SUM)
// { "North", 15; "South", 13; "Total", 28 }

GROUPBY(A1:A6, C1:C6, SUM, 3)
// Shows the header row: { "Region", "Qty"; "North", 15; "South", 13; "Total", 28 }

GROUPBY(A2:B6, C2:C6, SUM, 0, 2)
// Two row fields with a subtotal per region and a "Grand Total" row

GROUPBY(A2:A6, C2:C6, HSTACK(SUM, AVERAGE))
// { "", "SUM", "AVERAGE"; "North", 15, 5; "South", 13, 6.5; "Total", 28, 5.6 }

GROUPBY(A2:A6, C2:C6, LAMBDA(x, MAX(x)), 0, 0, -2)
// Custom aggregation, no total row, sorted by value descending: { "North", 10; "South", 8 }

GROUPBY(A2:A6, C2:C6, SUM, 0, 1, 1, C2:C6>4)
// Only rows with Qty above 4: { "North", 10; "South", 13; "Total", 23 }

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.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>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=GROUPBY(A1:A6, C1:C6, SUM, 3)"
    ],
    [
        "North",
        "Pen",
        10,
        2.5
    ],
    [
        "South",
        "Pen",
        5,
        2.5
    ],
    [
        "North",
        "Book",
        2,
        12
    ],
    [
        "South",
        "Book",
        8,
        12
    ],
    [
        "North",
        "Pen",
        3,
        2.5
    ]
]
  }]
});
</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('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=GROUPBY(A1:A6, C1:C6, SUM, 3)"
    ],
    [
        "North",
        "Pen",
        10,
        2.5
    ],
    [
        "South",
        "Pen",
        5,
        2.5
    ],
    [
        "North",
        "Book",
        2,
        12
    ],
    [
        "South",
        "Book",
        8,
        12
    ],
    [
        "North",
        "Pen",
        3,
        2.5
    ]
];

    // 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('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=GROUPBY(A1:A6, C1:C6, SUM, 3)"
    ],
    [
        "North",
        "Pen",
        10,
        2.5
    ],
    [
        "South",
        "Pen",
        5,
        2.5
    ],
    [
        "North",
        "Book",
        2,
        12
    ],
    [
        "South",
        "Book",
        8,
        12
    ],
    [
        "North",
        "Pen",
        3,
        2.5
    ]
]

        return {
            data
        };
    }
}
</script>

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

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NTAzZTEyYWUwMzg5ZWJmMmYxMmFhOGUwMjgxNjcyMzRiOWQ1MjJmYzhmNTVkM2EwYzI4NzNkMzE2YTNiYWE4NWYwMDI4ZDllOTdmMWMwN2Q4NDI4MjYxNTEwOTdmOWZhNjE3NTA1ODU1MDk1YzlhZjNhMmExZGNhZGZjYWVhMDgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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: [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=GROUPBY(A1:A6, C1:C6, SUM, 3)"
    ],
    [
        "North",
        "Pen",
        10,
        2.5
    ],
    [
        "South",
        "Pen",
        5,
        2.5
    ],
    [
        "North",
        "Book",
        2,
        12
    ],
    [
        "South",
        "Book",
        8,
        12
    ],
    [
        "North",
        "Pen",
        3,
        2.5
    ]
]
            }]
        });
    }
}