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: