Old Lotus Sub Total & Grand Total formula

[Kylie]

In the old days I remember a sub total and grand total formula in Lotus. How
can I achieve the same results in Excel?

Column A
2
2
sub total formula = 4

3
3
sub total formula = 6

grand total formula = 10

The grand total formula would only add up all the cells in the column and
exclude the sub total formulas.

Or am I dreaming?

 

[Biff]

Goto the Data menu and select Subtotals

Biff

 

[de vlam johan]

the formula you require is: "subtotal(9;range)". the first parameter is 9 if
you require subtotals being made in combination with the autofiltersystem.
below you find an examle.
clientname jan feb mar total
ABC Wireless 500 300 50 850
Top Wireless 400 250 50 700
ABC Wireless 250 250 50 550

totals 2650 1550 300 4500 Selection in % of grand total
subtotals 1150 800 150 2100 46,67%


If A1:A6 ABC Wireless is found than add the B,C,D columns that's in same row

and sum it in E row
column headings can contain month info or product info; time related info
can be added in each record(row) if so required.

1 Understand you have a database which should give info at regular intervals
or at any time you like.
2 Totalising more than one record(row) in column E cells would create
interpretation risks.
3 Suggest you use the "autofilter" in combination with the "subtotal"
formula.
4 You should avoid the risk of typing differences in the name of
clients.(especially if yr database is fed by various people)

autofilter: Select yr databank;in menu DATA select FILTER and
AUTOFILTER.
SUBTOTAL is a formula with a most interesting first parameter. In this case
use 9. formula reads as follows: =SUBTOTAAL(9;B4:B9)
FYG test also other first parameters than 9.If yet unknown it will upgrade
yr knowledge of excell.