SUMPRODUCT function

PRO

The SUMPRODUCT function in Jspreadsheet Formulas Pro is a powerful tool that multiplies corresponding items in the given arrays or ranges and then sums those results. Imagine you have two columns of numbers, and you want to multiply each pair of numbers together and then add all those results up. Instead of doing this manually, you can use SUMPRODUCT to do it all in one step. It streamlines the process, making it much quicker and easier to handle large amounts of data.

Documentation

Returns the sum of the products of corresponding ranges or arrays.

Syntax

SUMPRODUCT(array1, [array2], ...)

Parameter	Description
`array1`	The first array or range to multiply and add.
`arrayN`	Optional. Additional arrays or ranges to multiply and add.

Behavior

The SUMPRODUCT function is a function that multiplies ranges or arrays together and returns the sum of products. It is a useful tool for performing complex calculations with multiple data sets. Here's how it typically behaves:

Empty cells: SUMPRODUCT treats empty cells as zero. When a range or array containing an empty cell is included in the function, that cell's contribution to the overall product-sum is zero.
Text: SUMPRODUCT does not process text and will treat any text entry as zero when performing calculations.
Booleans: If boolean values are included in the arrays, SUMPRODUCT will treat TRUE as 1 and FALSE as 0.
Errors: If an error is present in one of the cells in the array, the SUMPRODUCT function will return an error. This could be any error like #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.

Common Errors

Error	Description
`#VALUE!`	This error typically occurs when the arrays or ranges that you've specified in the `SUMPRODUCT` function do not have the same dimensions.
`#REF!`	This error is seen when you include a cell reference that's not valid. This might be because the referenced cell has been deleted.
`#N/A`	This error is seen when the function includes a cell or range of cells that contain `#N/A` error.

Best practices

Always ensure that the arrays or ranges included in the SUMPRODUCT function have the same dimensions. Mismatched dimensions will return a #VALUE! error.

Avoid including text entries in the cells that SUMPRODUCT will process. The function treats text entries as zeros which may skew your results.

Use absolute cell references if you plan to copy or move your SUMPRODUCT formula. This ensures that the correct cells are always referenced.

Always check for errors in your data before applying the SUMPRODUCT function. The function will return an error if an error is present in any of the cells in the array.

Usage

A few examples using the SUMPRODUCT function.

SUMPRODUCT(A2:A6, B2:B6) returns the sum of the products of each pair of numbers in cells A2 through A6 and B2 through B6  
SUMPRODUCT([1, 2, 3], [4, 5, 6]) returns 32 (the sum of the products of each pair of numbers in the two arrays)  
SUMPRODUCT(A2:C6, D2:F6) returns the sum of the products of each set of three corresponding numbers in the two ranges

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('ZjY2MzIwYzZmNzAyNDVlZGRjN2IzNWMzZDM4Mjc0OGY3MDc2ODgxYzc1MmYwZmFkNzVlNmFmNmZhNmQwODM3ZDlmMzQ0NTQwMGEyYzYzMmM4M2IzY2FkZTUwNGZmN2JkYWQ2NGIzMDllZTZlYjY0MzQ3NmQyODU1MzhlMTZkNzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQXpPVEV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product",
        "Price",
        "Quantity",
        "Revenue"
    ],
    [
        "Laptops",
        800,
        5,
        "=SUMPRODUCT(B2:B4,C2:C4)"
    ],
    [
        "Tablets",
        300,
        8
    ],
    [
        "Phones",
        600,
        12
    ]
]
  }]
});
</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('ZjY2MzIwYzZmNzAyNDVlZGRjN2IzNWMzZDM4Mjc0OGY3MDc2ODgxYzc1MmYwZmFkNzVlNmFmNmZhNmQwODM3ZDlmMzQ0NTQwMGEyYzYzMmM4M2IzY2FkZTUwNGZmN2JkYWQ2NGIzMDllZTZlYjY0MzQ3NmQyODU1MzhlMTZkNzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQXpPVEV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Product",
        "Price",
        "Quantity",
        "Revenue"
    ],
    [
        "Laptops",
        800,
        5,
        "=SUMPRODUCT(B2:B4,C2:C4)"
    ],
    [
        "Tablets",
        300,
        8
    ],
    [
        "Phones",
        600,
        12
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product",
        "Price",
        "Quantity",
        "Revenue"
    ],
    [
        "Laptops",
        800,
        5,
        "=SUMPRODUCT(B2:B4,C2:C4)"
    ],
    [
        "Tablets",
        300,
        8
    ],
    [
        "Phones",
        600,
        12
    ]
]

        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('ZjY2MzIwYzZmNzAyNDVlZGRjN2IzNWMzZDM4Mjc0OGY3MDc2ODgxYzc1MmYwZmFkNzVlNmFmNmZhNmQwODM3ZDlmMzQ0NTQwMGEyYzYzMmM4M2IzY2FkZTUwNGZmN2JkYWQ2NGIzMDllZTZlYjY0MzQ3NmQyODU1MzhlMTZkNzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQXpPVEV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "Product",
        "Price",
        "Quantity",
        "Revenue"
    ],
    [
        "Laptops",
        800,
        5,
        "=SUMPRODUCT(B2:B4,C2:C4)"
    ],
    [
        "Tablets",
        300,
        8
    ],
    [
        "Phones",
        600,
        12
    ]
]
            }]
        });
    }
}

Edit this page on GitHub