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(B2:B5,C2:C5)

SUMPRODUCT - Sample Table
SUMPRODUCT – Sample Table

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

I.e.

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

Limits

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.

SUMPRODUCT Behaves like SUM
SUMPRODUCT Behaves like SUM

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.

=SUMPRODUCT(–(A1:A7=”galaxy”))

COUNTIF functionality using SUMPRODUCT
COUNTIF functionality using SUMPRODUCT

Above formula breaks down to an array –
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

The double negative forces it to become
{0;0;1;0;0;1;0}

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

Conclusion

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 libreofficehelp.com on Twitter, Facebook, and subscribe to our YouTube channel and email updates via below form.

Join 522 other subscribers.