Interpretation of SUBTOTAL() operations argument

Introduction

This is part of a series on replacing the the SUBTOTAL() worksheet function.  It is meant to provide a better explanation than Microsoft provides regarding the 1-11 and 101-111 ranges for the first argument and to explain why in SUBTOTAL() function for rows with hidden columns  (and subsequent posts) I chose to use the traditional 1-11 range.

What Microsoft says

Below is a table of information, from the Help for the SUBTOTAL() function, regarding the "Function_num" arguments.

Function_num (includes hidden values)	Function_num (ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

The help for SUBTOTAL() further says "For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list. "

This wasn't clear to me, so I tried it out.

The Test Base

Here is how the test is set up

• A1 ="Group"; A2 = "How Much?"
• Put column of randomized numbers between 1 and 10 in B2:B21
  NOTE:  "Randomized" means I used RANDBETWEEN(1,10) for the entries then used Copy/Paste Values.
• Used the Name function to name the randomized numbers "HowMuch"
• Each row labeled as Group1, Group2, or Group3 in A2:A21.
• Set AutoFilter in A1 for A2:A21.
• B23 = "=SUBTOTAL(9,HowMuch)"
  B24 = "=SUBTOTAL(9,HowMuch)"

The Tests

Filter Off, no rows hidden

B23 = 110  (includes hidden values)
B24 = 110 (ignores hidden values)

Filter Off, Five rows hidden

B23 = 110(includes hidden values)

B24 =   75 (ignores hidden values)

Filter On, showing only Group 1, no other rows hidden

B23 = 45 (includes hidden values)
B24 = 45 (ignores hidden values)

Filter On, showing only Group 1, Two Group1 rows hidden

B23= 34 (includes hidden values)
B24= 34 (ignores hidden values)

In a table..

Settings		Results
Filter	Other Hidden Rows	B23 (includes hidden values)	B24 (ignores hidden values)
Off	No	110	110
Off	Yes	110	75
On	No	28	28
On	Yes	45	45

Conclusion

The "ignores hidden values" range only has an impact when the filter is OFF. Therefore, for the functions to complement and replace SUBTOTAL() the 1-11 range will be used.