Subtotals

[Deborah]

What does the 9 in this formula mean "=SUBTOTAL(9,J2:J105)"?

 

[Bernard Liengme]

The syntax is =SUMTOTAL(function_number, reference_1, [reference_2]....)
The function_number arguments informs Excel what you require of SUBTOTAL as
shown in the table below taken from Help.
The value 9 means you want to SUM (rather than count, average, etc) and as
it is a single digit you want to the sum to include any hidden values.


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

best wishes

 

[David Biddulph]

As far as I can see, the Excel Help for the SUBTOTAL function makes it
perfectly clear. Have you looked at Help? Was there something there which
you didn't understand?

 

[Shane Devenshire]

Hi,

Actually the 9 means two things:

1. The operation you want to perform is SUM
and
2. The entries you want to sum are hidden via the AutoFilter command, not
by manually hidding the rows or collapsing groups (as in the Subtotal
command).
3. If you want to sum only visible cells choose 109 instead of 9. Then it
doesn't matter how you hide the rows.