SUMIF and SUMPRODUCT Functions in Calc

From NeoWiki

The SUMIF function belongs to the mathematical functions category and the SUMPRODUCT function to the array functions one. They allow you to work with cell ranges which meet some criteria. Before describing them, it's good to explain the difference between a function (or formula) and an array function (or array formula).

Formulas and Array Formulas

An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press Command-Shift-Return. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.

Example

Let us suppose that the range A1:A20 contains numeric values. To display the product of 5 and each value of this range in cells B1:B20, we have two possibilities:
1) Click in cell B1, enter the formula:

=A1*5

and press the Return key. Then fill the range B1:B20 by dragging the pointer. See Selecting and Filling a Cell Range in NeoWiki.

2) Click in cell B1, enter the formula:

=A1:A20*5

and press Command-Shift-Return.
The range B1:B20 is automatically filled.

SUMIF Function

This function is a mathematical function, not an array formula. It allows you to sum a cell range which constitutes a matrix and meets some criteria. These criteria must be a number or a string. The syntax is:

=SUMIF(range _to_be_tested;criteria;sum_range)

Let us suppose that the range A1:A20 contains numeric values.

• To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.
  

• In cell B1, enter the formula :

=ISODD(A1:A20)

and press Command-Shift-Return.

• In an empty cell, enter the formula:

=SUMIF(B1:B20;1;A1:A20)

or

=SUMIF(B1:B20;"TRUE";A1:A20)

and press Return.

• To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the following formula in an empty cell:

=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)

Note – You can replace the SUMIF function with the array formula SUM. The syntax of this function is:

=SUM((range=criteria)*(sum_range))

For example, in the above cases, you can enter:

=SUM((B1:B20=1)*(A1:A20))

or

=SUM((A1:A20>AVERAGE(A1:A20))*(A1:A20))

in an empty cell, and press Command-Shift-Return.

SUMPRODUCT Function

We suppose again that the range A1:A20 contains numeric values.

The SUMPRODUCT function is an array function. As it returns a single number, it can be entered by pressing only the Return key. It can be used:
1) To calculate the scalar product of two vectors.

2) To get the number of cells in a range which meet some criteria. The syntax is:

=SUMPRODUCT(range=criteria)

• Let us count the number of cells which contain odd numbers:

In an empty cell, enter the formula:

=SUMPRODUCT(ISODD(A1:A20))

and press Return.

• Let us count the number of cells which contain numbers greater than the range average:

In an empty cell, enter the formula:

=SUMPRODUCT(A1:A20>AVERAGE(A1:A20))

and press Return.

• Let us count the number of cells which contain numbers which are odd and greater than the range average:

In an empty cell, enter the formula:

=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20)))

and press Return.

3) To add the numbers in a range which meet some criteria. The syntax is:

=SUMPRODUCT(sum_range;criteria)

or

=SUMPRODUCT((sum_range)*(criteria))

In each case, you can change the order of the factors.

• Adding the odd numbers of a range:

  In an empty cell, enter one of the following formulas:

=SUMPRODUCT(A1:A20;ISODD(A1:A20))

or

=SUMPRODUCT((A1:A20)*(ISODD(A1:A20)))

and press Return.

• Adding the numbers greater than the range average:

  In an empty cell, enter the formula:

=SUMPRODUCT(A1:A20;A1:A20>AVERAGE(A1:A20))

and press Return.

• Adding the numbers odd and greater than the range average:

  In an empty cell, enter the formula:

=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20))*(A1:A20))

and press Return.

Note Defining cell ranges can be helpful.
Let us suppose that the cells of the range A1:A10 contain the value "red" or the value "green", and that the cells of the range B1:B10 contain the value "big" or the value "small". To count the number of simultaneous occurrences of the values "red" and "small", we can enter the formula:

=SUMPRODUCT(A1:A10="red";B1:B10="small")

or

=SUMPRODUCT((A1:A10="red")*(B1:B10="small"))

in C1.
But we can also define the ranges A1:A10 and B1:B10. To do that:

• Select the range A1:A10.
• Go to the Data menu and choose Define Range….
• In the Define Range Database window which appears, enter a Name, for example "color" (without quotes).
• Click on Add then on OK.
• Do the same with the range B1:B10 which you can call "size"
• You can now use the formula:

=SUMPRODUCT(color="red";size="small")

or

=SUMPRODUCT((color="red")*(size="small")).