SUMPRODUCT Function with Examples in LibreOffice Calc


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.

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.

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.

Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.

Join our Telegram channel and stay informed on the move.


Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments
John Atwell

How do I get negative currency numbers to display in RED?

Would love your thoughts, please comment.x