SUM function

SUM adds all the numbers in a range of cells.

Syntax

SUM(number1, number2, ...number30)

number 1 to number 30 are up to 30 arguments whose sum is to be calculated.

Example

If you enter the numbers 2, 3, and 4 in the Number 1, 2, and 3 text boxes, 9 is returned as the result.

=SUM(A1,A3,B5) calculates the sum of the three cells. =SUM (A1:E10) calculates the sum of all cells in the A1 to E10 range.

Conditions linked by AND can be used with the function SUM() in the following manner:

Example assumption: You have entered invoices into a table. Column A contains the date value of the invoice, column B contains the amounts. You want to find a formula that you can use to return the total of all amounts for a specific month only. For example, you want to return only the amount for the period >=2008-01-01 to <2008-02-01. The range with the date values covers cells A1:A40, the range containing the amounts to be totaled is in cells B1:B40. C1 contains the start date, 2008 -01-01, of the invoices to be included, and C2 the date, 2008 -02-01, that is no longer included.

Enter the following formula as an array formula:

=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)

To enter this formula as an array formula, you must press the Shift+ Ctrl+Enter keys instead of pressing the Enter key to close the formula. The formula is then shown in the formula bar enclosed in braces.

{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}

The formula is based on comparison results of 1 if the criterion is met and 0 if it is not met. The individual comparison results are treated as an array and used in matrix multiplication, and then the individual values are totaled to give the result matrix.




Trademarks | IBM Connections wiki