Arithmetic Functions

EasySpreadsheet includes functions for adding and multiplying lists of numbers, or ranges of cells.

Sums

SUM (number_list): Returns the sum of a list of numbers.

SUMSQ (number_list): Squares each of the supplied numbers and returns the sum of the squares.

SUMPRODUCT (range_list): Multiplies the corresponding cells in the given ranges, then returns the sum of those products. The ranges in the range_list must be of the same dimensions (i.e., each cell range must have the same number of rows, and the same number of columns).

Examples:

 

A

B

C

1

1

2

3

2

5

4

1

3

2

8

1

 

Formula

Result

=SUM(2, 3, 8, 1)

2+3+8+1 = 14

=SUM(A1:B3)

1+5+2+2+4+8 = 22

=SUM(A1, C2, B3)

1+1+8 = 10

=SUMSQ(B1:B3)

2*2 + 4*4 + 8*8 = 84

=SUMPRODUCT(B1:B2, C1:C2)

2*3 + 4*1 = 10

=SUMPRODUCT(A1:A3, B2:B3)

#VALUE!  (cell ranges of different dimensions)

=SUMPRODUCT({2, 4, 1}, {3, 5, 2})

2*3 + 4*5 + 1*2 = 28

 

Conditional Sums

SUMIF (range, criteria, sum_range): Examines the range of cells based on the given criteria, and returns a sum. The criteria must be enclosed in double-quotes, and can be a value ("10"), a text string ("Pass"), or an expression ("<50"). If no sum_range is given, then the range values that meet the criteria will be summed. If a sum_range is given, then the sum_range values in the corresponding cells will be summed.       

Examples:

 

A

B

C

1

75

20

Yes

2

83

19

Yes

3

52

21

No

4

75

19

No

 

Formula

Result

=SUMIF(A1:A4, "75")

75+75 = 150

=SUMIF(A1:A4, "75", B1:B4)

20+19 = 39

=SUMIF(B1:B4, ">19")

20+19 = 41

=SUMIF(B1:B4, "<=20", A1:A4)

75+83+75 = 233

=SUMIF(C1:C4, "No", B1:B4)

21+19 = 40

=SUMIF(C1:C4, "No")

0

 

Multiplication and Division

PRODUCT (number_list): Multiplies a list of numbers and returns the result.

MOD (number, divisor): Returns the remainder after dividing the number by the divisor.

SQRT (number): Returns the square root of the number.

Examples:

Formula

Result

=PRODUCT(2, 3, 2, 1)

2*3*2*1 = 12

=MOD(9, 3)

0

=MOD(11, 3)

2

=MOD(7, 2)

1

=MOD(1, 0)

#DIV/0!

=SQRT(4)

2

=SQRT(15)

3.872983346

 

Absolute Value and Sign Functions

ABS (number): Returns the absolute value of the number. For example, both ABS(5) and ABS(-5) will return 5.

SIGN (number): Determines the sign of the number. Returns 1 if the value is positive, and -1 if the value is negative. Note that SIGN(0) returns 0.

 

See Also:

Using the SUM Function