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)

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

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.

### 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.

### Single Range

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

### 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”))

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.

### Sum of all cells length

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