What is Function Numbers in Google Sheets

0
313
What is Function Numbers in Google Sheets

We are not fully exploiting the functions in a spreadsheet if we are not aware of functions numbers. Then what is function numbers in Google Sheets? I will explain the all available function numbers in Google Sheets and its use with examples.

What is Function Numbers in Google Sheets and How it Affect Hidden Rows

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

Note: Function numbers are only applicable in Column of Data or Vertical ranges.

Before going to the available list of function numbers in Google Sheets you should know what are those functions where we can apply function numbers.

List of Functions Where We Can Apply Function Numbers

Function numbers are applicable to the following Google Sheet functions. Also each functions there are two function numbers. The use of it depends whether you want to include hidden row values in result or not.

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

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.

Didn’t get? You can use below function numbers together with SUBTOTAL function as an alternative to above 11 function numbers! That means function numbers replaces the above eleven Google Sheet functions.

List of Function Numbers in Google Sheets

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, and finally 111 for VARP

Below are the examples to the usage of function numbers in Google Sheets. Learn it as it’s very useful in calculations.

  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)

Caution:

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