Sum function examples
1999-08-14 Sum 0 209
A semicolon (;) is used in all examples as the list separator, replace it with a comma (,) if necessary.
| Description | Formula |
|---|---|
| Add the numbers in E4:E23 | =SUM(E4:E23) |
| Add the numbers in G4:G26. Ignores the result from other SUBTOTAL-functions in the range. Includes visible cells only. | =SUBTOTAL(9;G4:G26) |
| Add the numbers in E4:E23 if they are greater than 100 | =SUMIF(E4:E23;">100") |
| Add the numbers in E4:E23 that "belongs" to department North, the departments are in the range H4:H23. | =SUMIF(H4:H23;"North";E4:E23) |
| Add the numbers in E4:E13 multiplied with F4:F13 | =SUMPRODUCT(E4:E13;F4:F13) |
| Same as above, but with an array formula | {=SUM(E4:E13*F4:F13)} |
| Adds the square root of the numbers S(x²) | =SUMSQ(E4:E13) |
| Returns S(x² - y²) | =SUMX2MY2(E4:E13;E14:E23) |
| Returns S(x² + y²) | =SUMX2PY2(E4:E13;E14:E23) |
| Returns S(x - y)² | =SUMXMY2(E4:E13;E14:E23) |
| Adds the numbers from every other row in the range (array formula) | {=SUM((E4:E23)*(MOD(ROW(E4:E23)-ROW(E4);2)=0))} |
| Adds the numbers from every 5th row in the range (array formula) | {=SUM((E4:E23)*(MOD(ROW(E4:E23)-ROW(E4);5)=0))} |
| Adds the even numbers in the range (array formula) | {=SUM(IF(MOD(I4:I23;2)=0;I4:I23;0))} |
| Adds the odd numbers in the range (array formula) | {=SUM(IF(MOD(I4:I23;2)<>0;I4:I23;0))} |
| Adds the range depending on two AND-criterias (array formula) | {=SUM(IF((E4:E23>50)*(H4:H23="North");E4:E23;0))} |
| Adds the range depending on two AND-criterias (array formula) | {=SUM((A5:A100="North")*(B5:B100=1999)*E5:E100)} |
| Adds the range depending on three AND-criterias (array formula) | {=SUM((A5:A100="North")*(B5:B100=1999)*(C5:C100=8)*E5:E100)} |
| Adds the range depending on two OR-criterias (array formula) | {=SUM((A5:A100="North")*E5:E100 + (A5:A100="South")*E5:E100)} |