ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

A collection of sum function examples

In this example a semicolon (;) is used as the list separator, repace 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((E5:E24>50)*(H5:H24="North")*E5:E24;0)}
Adds the range depending on two OR-criterias (array formula) {=SUM((H4:H23="North")*E4:E23+(H4:H23="South")*E4:E23)}

More examples on how to add timevalues.

 

Document last updated 1999-08-14 12:44:13      Printerfriendly version

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address