using functions after (using autofilter)

[_Bigred]

Hello All,

(Excel 2000)

If I have a sheet and use auto filter, I will would like to be able sum,
average etc... it's results without fixing the cell range(s) every time I
change the autofilter "criteria".


e.g. Column N is classification - I select "Correctional Officer" it
returns just correctional officers. then want to sum Column U's results
which are hourly pay.

First record is Row 269 (but each records retains it's original row #, from
before the autofilter). And If I select a different classification it would
return the original row #'s for the records being filtered etc..

How do I create a sheet that sums =SUM(2003Frozen!U?:U?) to sum just
the results of the autofilter??

TIA,
_Bigred

 

[Gord Dibben]

BigRed

Check out Help on SUBTOTAL

To SUM you would use =SUBTOTAL(9,range)

SUBTOTAL does not add the hidden rows whereas SUM does.

Gord Dibben XL2002

 

[Debra Dalgleish]

Use the Subtotal function -- it returns results for the rows that are
visible after a filter has been applied.

Look in Excel's Help for information on Subtotal and its arguments. For
example, =SUBTOTAL(9,U:U) would return the sum of the visible rows.

 

[_Bigred]

I will check it out, and post my findings.

Thanks,
_Bigred

 

[_Bigred]

That worked like a top, thanks guys.

Thanks,
_Bigred