What is Function Numbers in Google Sheets

Most of us hide rows in Google Sheets. Also it’s common to apply filter command to get the desired result. If hidden rows are in your data, you should know about function numbers for ease of work as well as to get correct result with your formulas. Function numbers are only applicable in Column of Data or Vertical ranges.

Related : How to Omit Hidden or Filtered out Values in Sum [Google Doc Spreadsheet]

Function numbers are applicable to the following Google Sheet functions.

AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP.

function numbers in google sheets

All of the above functions can be replaced with only one function in conjunction with function numbers. The function is SUBTOTAL. In other words SUBTOTAL is one of the most useful function in a spreadsheet.

Below are the function numbers for the above Eleven functions. We can use these function numbers with SUBTOTAL so that there is no need to use the above 11 functions!

101 for AVERAGE

102 for COUNT

103 for COUNTA

104 for MAX

105 for MIN

106 for PRODUCT

107 for STDEV

108 for STDEVP

109 for SUM

110 for VAR

111 for VARP

  A B C
1 Average Count CountA
2 50 50 50
3 50 50 A
4 50 50 50
5 =subtotal(101,A1:A3) =subtotal(102,B1:B3) =subtotal(103,C1:C3)
  D E F
1 Max Min Product
2 70 70 50
3 60 60 50
4 50 50 50
5 =subtotal(104,D1:D3) =subtotal(105,E1:E3) =subtotal(106,F1:F3)
  G H I
1 Stdev Stdevp Sum
2 12 12 50
3 25 25 50
4 14 14 50
5 =subtotal(107,G1:G3) =subtotal(108,H1:H3) =subtotal(109,I1:I3)
  J K
1 Var Varp
2 12 12
3 25 25
4 14 14
5 =subtotal(110,J1:J3) =subtotal(111,K1:K3)

In the above example, if you hide any row, the value will be omitted from the total. It’s the important part. If you want the hidden rows value to be reflected in the total, still there are options. Use function numbers 1,2,3,4,5,6,7,8,9,10 and 11 instead of the the above function numbers.

LEAVE A REPLY

Please enter your comment!
Please enter your name here