This tutorial explains how to perform SUMPRODUCT in LibreOffice Calc.

SUMPRODUCT returns the sum of products of corresponding array elements. For example if you perform SUMPRODUCT in below example table –


SUMPRODUCT - Sample Table
SUMPRODUCT – Sample Table

It would return the result of (B2*C2+B3*C3+B4*C4+B5*C5).


The result would be (10*100+20*200+17*150+11*50) = 8100

SUMPRODUCT - Example 1
SUMPRODUCT – Example 1

This is the basic working principle of SUMPRODUCT which you can use for various needs. Here are some features/tricks of SUMPRODUCT.

More Examples

Same Size Array

SUMPRODUCT arrays needs to be in same size, otherwise Calc would give #VALUE! Error. For example, if you change the formula to C2:C4, it will give you below error.

Same Size Array Error
Same Size Array Error


LibreOffice Calc can provide SUMPRODUCT up to 30 arrays.


Non-Numeric or alpha Values

If your ranges are having non-numeric values, Calc considers it as 0 and carry on with the SUMPRODUCT.

SEE ALSO:  How to Add Numeric Data Validation in Calc Spreadsheet
Alpha Values in SUMPRODUCT
Alpha Values in SUMPRODUCT

Single Range

SUMPRODUCT behaves like SUM if only array/range is provided as below.


Advanced Usage of SUMPRODUCT

Count Items Using SUMPRODUCT in a Range

It is possible to achieve certain COUNTIF function results using SUMPRODUCT. For example, if you would like to count a specific string in a range of cells using SUMPRODUCT do the below.


COUNTIF functionality using SUMPRODUCT
COUNTIF functionality using SUMPRODUCT

Above formula breaks down to an array –

The double negative forces it to become

SUMPRODUCT uses this array to sum and shows the count = 2.

You can use other wild card (e.g. “?”) characters to achieve different results as explained in this tutorial.

SEE ALSO:  How to Delete Empty Rows in LibreOffice Calc Sheet

Sum of all cells length

By combining LEN() function and SUMPRODUCT, you can find out the total length of a range of cells.

Length Calculations using SUMPRODUCT
Length Calculations using SUMPRODUCT


SUMPRODUCT is a powerful function and can be utilized in various ways. It can reduce the complications of using COUNTIF in certain cases and achieve identical results.

Drop a comment if this article helped you or if you have any questions.

For the latest tutorials, news and how-to guides, follow on Twitter, Facebook, and subscribe to our YouTube channel and email updates via below form.

Join 522 other subscribers.