Subtotal Formula

P

Pat

Hi! ;)

When subtotals have been created in an excel file - when viewing the formula
in the address bar what does the "9" stand for: =SUBTOTAL(9,H2:H23)
 
P

Pam

Believe it or not, the "9" stands for SUM. See table below for other possible
choices:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
 
R

Ron Coderre

The SUBTOTAL function is multipurpose.....
The first argment value tells it what kind of value to return:

Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

In your case.....9 returns a SUM

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
P

Peo Sjoblom

It is all explained in the help file for the SUBTOTAL function,
9 stands for SUM, 2 for COUNT and so on
 
C

Carla

OK, in my subtotal formula what would 103 and 109 stand for?
=SUBTOTAL(103,A1:A6148
=SUBTOTAL(109,A1:A6148
 
P

Peo Sjoblom

COUNTA and SUM, but these will not only sum or count visible cells from
filtering they will do it for hidden cells as well. These were introduced in
Excel 2003 and they just added 100 to the previous subtotal. It's in help as
well

--


Regards,


Peo Sjoblom
 
C

Carla

Can you tell me exactly where in help this list would be? I can't seem to
find it.
 
P

Peo Sjoblom

Do you have Excel 2003 or later? Just look up SUBTOTAL in help (comes up as
SUBTOTAL worksheet function)


--


Regards,


Peo Sjoblom
 
Top