DocumentationFormulasFunctionsPivotby

PIVOTBY function

PRO

The PIVOTBY function in Jspreadsheet Formulas Pro builds a pivot table with a single formula. It distributes the unique values of one field down the rows and the unique values of another field across the columns, then aggregates the values that fall into each intersection. The result is a dynamic array that includes row and column totals by default and recalculates automatically when the underlying data changes, which makes it a convenient alternative to a manual pivot table for dashboards and reports.

Documentation

Cross-tabulates a range by row and column fields and aggregates the associated values, returning a pivot table.

Category

Lookup and reference

Syntax

PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

Parameter Description
row_fields A column-oriented range or array whose unique values become the rows of the pivot table.
col_fields A column-oriented range or array whose unique values become the columns of the pivot table.
values A column-oriented range or array with the values to aggregate. It must have the same number of rows as the field ranges. Each additional column produces one more result column per column field value.
function The aggregation applied to each intersection, such as SUM, AVERAGE, COUNT, MAX or a LAMBDA. Use HSTACK(SUM, COUNT) to apply several aggregations.
[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.
[row_total_depth] Optional. 0 = no row totals, 1 = a grand total row (default), 2 = grand total and one subtotal per first-level row field. Negative values place the totals at the top.
[row_sort_order] Optional. The row key column or the totals column to sort the rows by, or an array of key columns such as {2,1}. Use a negative number for descending order.
[col_total_depth] Optional. 0 = no column totals, 1 = a grand total column (default), 2 = grand total and one subtotal per first-level column field. Negative values place the totals on the left.
[col_sort_order] Optional. The column key row or the totals row to sort the columns by, or an array of key rows. 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.
[relative_to] Optional. Used with PERCENTOF or a two-argument LAMBDA to choose the base of the percentages: 0 = column totals (default), 1 = row totals, 2 = grand total, 3 = parent column total, 4 = parent row total.

Behavior

The PIVOTBY function returns a dynamic array whose first row lists the unique column field values, whose first column lists the unique row field values, and whose body holds the aggregated value of each intersection.

  • By default the result includes a Total row and a Total column. Set row_total_depth or col_total_depth to 0 to remove either of them. With two or more row fields, row_total_depth 2 adds a subtotal row per first-level group and a Grand Total row; the same applies to column fields with col_total_depth.
  • When field_headers is omitted, a header row in the ranges is detected automatically. With field_headers equal to 3, the name of the column field is shown above the column values and the names of the row field and of the values column are shown as labels.
  • Intersections without matching rows are left empty rather than filled with 0.
  • Rows and columns are sorted in ascending order by their field value. A negative row_sort_order or col_sort_order reverses the order.
  • When HSTACK passes several functions, each column field value is repeated once per function and an extra header row shows the function names.
  • A LAMBDA receives the values of each intersection as an array, allowing custom aggregations such as LAMBDA(x, MAX(x)).
  • The filter_array is applied before the table is built, so totals only reflect the rows that pass the filter.

Common Errors

Error Description
#VALUE! Returned when the field ranges, 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 an option such as field_headers or a total depth receives 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 row and a column of their own.
  • Keep the area below and to the right of the formula empty. The table grows with the number of unique values in both fields.
  • Use row_total_depth 0 and col_total_depth 0 when the totals will be calculated elsewhere, to keep the output compact.
  • Use filter_array with a comparison such as C2:C6>4 instead of maintaining a filtered copy of the data.
  • Prefer a single value column per formula. Several value columns multiply the number of result columns and make the table harder to read.

Usage

A few examples using the PIVOTBY function.

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

PIVOTBY(A2:A6, B2:B6, C2:C6, SUM)
// { "", "Book", "Pen", "Total"; "North", 2, 13, 15; "South", 8, 5, 13; "Total", 10, 18, 28 }

PIVOTBY(A1:A6, B1:B6, C1:C6, SUM, 3)
// Shows the "Product", "Region" and "Qty" labels taken from the header row

PIVOTBY(A2:A6, B2:B6, C2:C6, AVERAGE, 0, 0)
// Averages without the total row

PIVOTBY(A2:A6, B2:B6, C2:C6, SUM, 0, 1, -1)
// Rows sorted descending: South before North

PIVOTBY(A2:A6, B2:B6, C2:C6, SUM, 0, 1, 1, 0)
// No total column

PIVOTBY(A2:A6, B2:B6, C2:C6, SUM, 0, 1, 1, 1, 1, C2:C6>4)
// Only rows with Qty above 4: { "", "Book", "Pen", "Total"; "North", "", 10, 10; "South", 8, 5, 13; "Total", 8, 15, 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('ZWYxZTkxOWIyZjc1ZWE2Yzc0MjRmNGI5MGM3ODNmZTMxODg3YmYyNTFjOGI1Njk4N2E0ZTIyNGZmY2Y2MTQ3YTJlM2QyYzI3YzMxOTU3ODM3ZjRmZmU5NjJjZWYxYjA5ZDZlNDc5ZjQ2YjkzOGE3ZTgyMTk2NGJiNjVkYzM0NTgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=PIVOTBY(A1:A6, B1:B6, 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('ZWYxZTkxOWIyZjc1ZWE2Yzc0MjRmNGI5MGM3ODNmZTMxODg3YmYyNTFjOGI1Njk4N2E0ZTIyNGZmY2Y2MTQ3YTJlM2QyYzI3YzMxOTU3ODM3ZjRmZmU5NjJjZWYxYjA5ZDZlNDc5ZjQ2YjkzOGE3ZTgyMTk2NGJiNjVkYzM0NTgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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",
        "",
        "=PIVOTBY(A1:A6, B1:B6, 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('ZWYxZTkxOWIyZjc1ZWE2Yzc0MjRmNGI5MGM3ODNmZTMxODg3YmYyNTFjOGI1Njk4N2E0ZTIyNGZmY2Y2MTQ3YTJlM2QyYzI3YzMxOTU3ODM3ZjRmZmU5NjJjZWYxYjA5ZDZlNDc5ZjQ2YjkzOGE3ZTgyMTk2NGJiNjVkYzM0NTgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Region",
        "Product",
        "Qty",
        "Price",
        "",
        "=PIVOTBY(A1:A6, B1:B6, 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('ZWYxZTkxOWIyZjc1ZWE2Yzc0MjRmNGI5MGM3ODNmZTMxODg3YmYyNTFjOGI1Njk4N2E0ZTIyNGZmY2Y2MTQ3YTJlM2QyYzI3YzMxOTU3ODM3ZjRmZmU5NjJjZWYxYjA5ZDZlNDc5ZjQ2YjkzOGE3ZTgyMTk2NGJiNjVkYzM0NTgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNekF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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",
        "",
        "=PIVOTBY(A1:A6, B1:B6, 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
    ]
]
            }]
        });
    }
}