HomeGoogle DocsSpreadsheetGoogle Sheets Function Numbers: A Comprehensive Guide

Google Sheets Function Numbers: A Comprehensive Guide

Published on

Function numbers are the function codes to use in subtotal aggregation in Google Sheets. They have many real-world uses, and they are a must-have when you deal with values in hidden cells.

Function numbers represent 11 of the most useful aggregation functions in Google Sheets, and you can use them to aggregate data presented in columns (vertical data), not rows.

This is certainly a drawback, but remember that we filter data in columns to filter out rows. In that sense, the vertical use of function codes is not a drawback.

At the time of writing this post, you can only use function numbers in Google Sheets in the SUBTOTAL function. However, in Excel, there is another function named AGGREGATE that also supports function numbers.

When should you replace your regular aggregation function with the SUBTOTAL + function number in Google Sheets?

There are three main instances when you might want to use function numbers in Google Sheets:

  1. When you want to omit hidden rows in the calculation range.
  2. When you want to place subtotals in a column.
  3. When you want to switch from one aggregation function to another function by calling a function number in a data validation drop-down.

The purpose of this tutorial is to provide you with the function numbers in Google Sheets. You can bookmark this page and check back when you want to use function codes in the SUBTOTAL function.

I’ll post all about SUBTOTAL in another tutorial.

Must Read: SUBTOTAL Function in Google Sheets: A Complete Guide

Google Sheets Aggregation Functions: Function Numbers and Use Cases

Eleven aggregation functions in Google Sheets have function number replacements. Each function has two function codes.

Here are the aggregation function names and their corresponding codes to use in SUBTOTAL.

Function NameCode ICode II
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111

Can you give me a basic example?

Sure. When you want to total the numbers in the range A1:A10, you usually use the following formula, right?

=SUM(A1:A10)

You can replace that with the following SUBTOTAL formulas:

=SUBTOTAL(9,A1:A10)
=SUBTOTAL(109,A1:A10).

In the following screenshot, the formulas above are in cells B1, B2, and B3, respectively. See how the formula results change when I hide/unhide two rows.

Function numbers in Google Sheets

How do the code I and code II sets of function numbers differ in function?

The code I set of function numbers excludes only cells hidden using the Data menu > Create a filter and Data menu > Add a slicer. The code II set of function numbers omits all types of hidden cell values.

Using Function Numbers in the SUBTOTAL Function in Google Sheets

Examples of Using All Function Numbers (Code II) in the SUBTOTAL Function in Google Sheets:

You can see below the 11 formulas and the results they return. Please note that the results are after hiding row 2 in the table.

Table #1 (Range A1:C5)

Table #1 contains three formulas that replace the AVERAGE, COUNT, and COUNTA functions.

 ABC
AverageCountCountA
1505050
25050A
3505050
=SUBTOTAL(101,A1:A3) // returns 50=SUBTOTAL(102,B1:B3) // returns 2=SUBTOTAL(103,C1:C3) // returns 2

Table #2 (Range D1:F5)

Table #2 contains three formulas that replace the MAX, MIN, and PRODUCT functions.

 DEF
MaxMinProduct
1707050
2606050
3505050
=SUBTOTAL(104,D1:D3) // returns 70=SUBTOTAL(105,E1:E3) // returns 50=SUBTOTAL(106,F1:F3) // returns 2500

Table #3 (Range G1:I5)

Table #3 contains three formulas that replace the STDEV, STDEVP, and SUM functions.

 GHI
StdevStdevpSum
1121250
2252550
3141450
=SUBTOTAL(107,G1:G3) // returns 1.41=SUBTOTAL(108,H1:H3) // returns 1=SUBTOTAL(109,I1:I3) // returns 100

Table #4 (Range J1:K5)

Table #4 contains two formulas that replace the VAR and VARP functions.

 JK
VarVarp
11212
22525
31414
=SUBTOTAL(110,J1:J3) // returns 2=SUBTOTAL(111,K1:K3)// returns 1

Conclusion

If you use function numbers 1 to 11 instead of 100 to 111, you will get the same results if you hide row 2 using the Data menu > Create a filter or Data menu > Add a slicer.

However, if you hide row 2 by right-clicking on the row number and clicking Hide row, or by using Grouping, the results of using functions numbers 1 to 11 will be the same as the regular aggregation functions.

Here are some resources where you can see the use of the SUBTOTAL function.

  1. Subtotal with Condition in Google Sheets [Step by Step Guide]
  2. Find the Average of Visible Rows in Google Sheets
  3. Google Sheets Query Hidden Row Handling with Virtual Helper Column
  4. SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column]
  5. How to Omit Hidden or Filtered Out Values in Sum [Google Doc Spreadsheet
  6. COUNTIF | COUNTIFS Excluding Hidden Rows in Google Sheets
  7. Grouping and Subtotal in Google Sheets and Excel
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here