
SUMIF and SUMPRODUCT Functions in Calc
From NeoWiki
Revision as of 18:39, 15 November 2007 (edit) Jgd (Talk  contribs) (→Formulas and Array Formulas  better wording (Thanks Lorinda, Samwise and Smokey)) ← Previous diff 
Revision as of 20:00, 15 November 2007 (edit) (undo) Samwise (Talk  contribs) m (→Formulas and Array Formulas  of the range in the range > of the range in cells) Next diff → 

Line 4:  Line 4:  
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 {{keyCommandShiftReturn}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br>  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 {{keyCommandShiftReturn}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br>  
:'''Example''' <br>  :'''Example''' <br>  
  Let us suppose that the range A1:A20 contains numeric values. To display the product of 5 and each value of this range in  +  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:<br> 
1) Click in cell B1, enter the formula:  1) Click in cell B1, enter the formula:  
::'''=A1*5'''  ::'''=A1*5''' 
Revision as of 20:00, 15 November 2007
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 CommandShiftReturn. 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 CommandShiftReturn.
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)
 =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 CommandShiftReturn.
 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))
 =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 CommandShiftReturn.
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))
 =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).
 Clck 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")).