SUM function

SUM adds all the numbers in a range of cells.


SUM(number1, number2, ...number30)

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


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:


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.


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.