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 the 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.
Table of Contents
More Examples
Same Size Array
SUMPRODUCT arrays need to be of the same size. Otherwise, Calc would give #VALUE! Error. For example, if you change the formula to C2:C4, it will give you the below error.
Limits
LibreOffice Calc can provide SUMPRODUCT with up to 30 arrays.
Non-Numeric or alpha Values
If your ranges have non-numeric values, Calc considers them 0 and carries on with the 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 want to count a specific string in a range of cells using SUMPRODUCT, do the below.
=SUMPRODUCT(--(A1:A7="galaxy"))
The 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 cards (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.
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.
How do I get negative currency numbers to display in RED?
You can use conditional formatting. Format -> Conditional -> Condition
Can this be used to return the name of the cell or even to locate the cells where identical values are?
Thank you,
No. SUMPRODUCT is not for that. If you want to find out the identical values in a column of data, use the filter. Or use Pivot to find out their number of occurrences.