|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

SUM

SUM

  • Last updated on

Description 

Use this function to return the sum of values supplied. Supported in Chains. Can be used with CHILDREFS.

 

Syntax

SUM(number, […, [number_254])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
number_1 Yes The first number to be added. A cell reference, a number, or a formula which results in either of these.  
number_n No Additional numbers to be added. A cell reference (including a range), a number, or a formula which results in either of these.  

 

Examples

Sample data

A B
1 51 57
2 93 45
3 egg 15
4 19 92
5 75 22

Sample formulas

Use case Formula Explanation and Result
Get the sum of all cells in a range (including hidden rows). =SUBTOTAL(9, B1:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 9: This function_code specifies the SUM operation and includes values in hidden rows.
  • B1:B5: The range to sum {57, 45, 15, 92, 22}.
  • The formula calculates 57 + 45 + 15 + 92 + 22.

For this data set, the formula returns 231.
Get the sum of a range while ignoring hidden rows. =SUBTOTAL(109, B1:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 109: This function_code specifies the SUM operation but ignores values in hidden rows.
  • B1:B5: The range to sum.
  • The formula calculates 57 + 45 + 15 + 92 + 22.

For this data set, the formula returns 231. 
Count the number of cells in a range that contain numbers. =SUBTOTAL(2, B2:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 2: This function_code specifies the COUNT operation (counts numbers only).
  • B2:B5: The range to count {45, 15, 92, 22}.
  • The formula counts the four numeric values.

For this data set, the formula returns 4.
Find the maximum value in a range. =SUBTOTAL(4, A1:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 4: This function_code specifies the MAX operation.
  • A1:B5: The rectangular range to evaluate.
  • The function finds the largest number in the range, ignoring the text "egg" in A3.

For this data set, the highest value is 93, and the formula returns 93.
Calculate the standard deviation of a sample. =SUBTOTAL(7, B$2:B$5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 7: This function_code specifies the STDEV.S operation (sample standard deviation).
  • B$2:B$5: The range to evaluate {45, 15, 92, 22}.
  • The function calculates the standard deviation for these four numbers.

For this data set, the formula returns approximately 37.058537.
Count the number of non-empty cells in a range. =SUBTOTAL(3, B2:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 3: This function_code specifies the COUNTA operation (counts non-empty cells).
  • B2:B5: The range to count {45, 15, 92, 22}.
  • The formula counts all four non-empty cells.

For this data set, the formula returns 4.
Find the minimum value in a range. =SUBTOTAL(5, B2:B5)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 5: This function_code specifies the MIN operation.
  • B2:B5: The range to evaluate {45, 15, 92, 22}.
  • The formula finds the smallest number in the range.

For this data set, the lowest value is 15, and the formula returns 15.

 

Notes

  • SUM automatically ignores empty cells.
  • If text is included by a cell or range reference, then it is treated as a zero.
  • If text is included without quotation marks, the function returns a #NAME! error. If text is included with quotation marks, the function returns a #VALUE! error.  
  • If arguments contain errors, SUM will return an error.
  • SUM can handle up to 255 total arguments.
  • Wildcards don't work with this function. 

 

Tips

TBD

 

Related functions

ABS
EXP
LN
MOD
N
POWER
PRODUCT
SIGN
SQRT
SUBTOTAL
SUMIF
SUMIFS
SUMPRODUCT

Was this article helpful?
0 out of 0 found this helpful
Description Use this function to return the sum of values supplied. Supported in Chains. Can be used with CHILDREFS. ...