|
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

SUBTOTAL

SUBTOTAL

  • Last updated on

Description 

Use this function to run a given calculation on a range of cells while ignoring cells that shouldn't be included. Supported in Chains. 

This function returns a value based any of the functions in the list below, and can either include or exclude values in hidden rows.

 

Syntax

SUBTOTAL(function_num, ref_1, […, ref_254])

 

Inputs

This function has the following arguments:

Name Required Description Valid input
function_num Yes The function to be used when creating the subtotal. A cell reference, a number, or a formula which results in either of these.  
ref_1 Yes The first value to be used when creating the subtotal. A cell reference, a number, or a formula which results in either of these.  
ref_n No Any additional values to be used when creating the subtotal. A cell reference, a number, or a formula which results in either of these. 

 

Function values

The function_num value identifies which calculation is to be performed for the subtotal.

The two values for each function allow you to choose to whether to include or exclude the values contained in hidden rows.

  • To include rows that have been hidden, use the 1–9 function_num values.
  • To exclude rows that have been hidden, use the 101–109 function_num values. 

 

Function Include hidden Ignore hidden
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEV.P 8 108
SUM 9 109

 

Examples

Sample data

A

B

C

D

E

1

Item

Price

Stock

Supplier

Rating

2

Apple

1.20

150

Fresh Fruits

4.5

3

Banana

0.50

300

Tropical Supplies

4.0

4

Grape

2.00

200

Vineyard Delights

4.7

5

Orange

1.00

250

Citrus World

4.3

6

Gizmo Z

$37.50

Electronics

(blank)

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 9: This function_code specifies SUM and includes values in hidden rows.
  • B2:B6: The range to sum {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • The function sums the numeric values and ignores the text value in B6.

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 109: This function_code specifies SUM and *ignores* values in hidden rows.
  • B2:B6: The range to sum {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • Assuming no rows are hidden, the function sums the numeric values and ignores text.

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 2: This function_code specifies COUNT (counts numbers only).
  • B2:B6: The range to count {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • The formula counts the four numeric values (B2, B3, B4, B5) and ignores the text in B6.

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 4: This function_code specifies MAX.
  • A2:B5: The rectangular range to evaluate.
  • The function finds the largest number in the range, ignoring all text values. The largest number is 2.00 (in B4).

For this data set, the formula returns 2.
Calculate the standard deviation of a sample. =SUBTOTAL(7, B$2:B$6)

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 7: This function_code specifies STDEV.S (sample standard deviation).
  • B$2:B$6: The range to evaluate {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • The function calculates the standard deviation for the numeric values {1.20, 0.50, 2.00, 1.00}, ignoring the text in B6.

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 3: This function_code specifies COUNTA (counts non-empty cells).
  • B2:B6: The range to count {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • The formula counts all 5 cells as non-empty.

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

This formula works in the following manner:

  • SUBTOTAL(: This is the function itself.
  • 5: This function_code specifies MIN.
  • B2:B6: The range to evaluate {1.20, 0.50, 2.00, 1.00, "$37.50"}.
  • The function finds the smallest number in the range, ignoring the text in B6.

For this data set, the formula returns 0.50.

 

Related Functions

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

Was this article helpful?
0 out of 0 found this helpful
Description Use this function to run a given calculation on a range of cells while ignoring cells that shouldn't be i...