Trying to understand a formula

  • Thread starter Thread starter smackedass
  • Start date Start date
S

smackedass

Hello

Someone in my organization has put together what I consider to be a very
well thought out spreadsheet to track inventory. When I attempt to analyze
it "from the inside out", I ran across a formula that I don't understand and
was unable to find in "Help".

Obviously, this formula counts, item by item, the rows within the range that
is in the formula. But can anyone tell me where I can find more information
on this specific formula:

=SUBTOTAL(3,C3:C62)

....and the number that appears in this cell is 58, which is the total of
integers between 3 and 62.

The really interesting thing about this is the formula updates itself
dynamically as new rows are added, when new inventory arrives.

Any help would be appreciated.

Thank you.

smackedass
 
Hello again,

I've done a bit more research, and I think that this might fall under the
category of a "pivot table". Am I on the right track?

Thanks again.

smackedass
 
it is either a pivot-table, or excel has a built in subtotal function on the
data menu (DATA|SUBTOTALS...)

HTH,

ryanb.
 
smack

If you can't find help on SUBTOTAL in Excel's help files you should do a
re-install/repair of Excel because you do not have full Help installed.

=SUBTOTAL(3,C3:C62) counts the number of items in that range. If range is
full you should get 60, not 58. You must have a couple of blank cells in that
range.

Gord Dibben XL2002
 
Thank you all for your timely replies.

Indeed there are 2 blank rows in that range...

I think that the only thing that I haven't figured out about this by now, is
what does the "3", followed by a comma, mean? I believe that it is a
"Summary Function"? However, I've yet to be able to find the list of
Summary Functions in Help.

Thank you again.

smackedass

Gord Dibben wrote in message ...
smack

If you can't find help on SUBTOTAL in Excel's help files you should do a
re-install/repair of Excel because you do not have full Help installed.

=SUBTOTAL(3,C3:C62) counts the number of items in that range. If range is
full you should get 60, not 58. You must have a couple of blank cells in that
range.

Gord Dibben XL2002
Click to expand...
 
Here are the list of the different subtotal functions

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

so to get the total you use

=SUBTOTAL(9,Range)

=SUBTOTAL(3,Range)

means that the formula counts visible cells regardless if they are text or
numbers

=SUBTOTAL(2,Range)

will count numbers



--

Regards,

Peo Sjoblom


smackedass said:
Thank you all for your timely replies.

Indeed there are 2 blank rows in that range...

I think that the only thing that I haven't figured out about this by now, is
what does the "3", followed by a comma, mean? I believe that it is a
"Summary Function"? However, I've yet to be able to find the list of
Summary Functions in Help.

Thank you again.

smackedass

Gord Dibben wrote in message ...
Click to expand...
 
Smacked,

Put the cell pointer on the cell with the SUBTOTAL function. Click the
arrow in the function box (it has the Autosum button in it). Then click
"More functions." You'll get a brief description of the Subtotal function.
You can click on each argument box for a description. You can click the
"Help on this function" link for detailed stuff on it.

Your handle might be a teeeensy bit offensive in these parts.
 
Thank you for the reply...my moniker is a sincere attempt at
self-deprication; I don't pretend to know a lot. My apologies to those whom
I may have offended.

Ken
 
Back
Top