Auto Filter totaling

[chipsta040365]

Hello everyone,

I have a report that i export to excel.

I setup autofilter, so they can sort the report any which way they like.

i.e. by rep, by style, by category,etc.

The report has a Cost field that i want to have an auto total box or window or cell that will always total no matter which sort method they use.

I know how to use the Autosum (Greek E), but i do not know how to put that calculation so that no matter what sort is used, it will only add what was sorted out.

Any Help Would be Greatly appreciated.

Thank You

 

[Aladin Akyurek]

Hello everyone,

I have a report that i export to excel.

I setup autofilter, so they can sort the report any which way they like.

i.e. by rep, by style, by category,etc.

The report has a Cost field that i want to have an auto total box or window or cell that will always total no matter which sort method they use.

I know how to use the Autosum (Greek E), but i do not know how to put that calculation so that no matter what sort is used, it will only add what was sorted out.

Any Help Would be Greatly appreciated.

Thank You

Maybe:

=SUBTOTAL(9,RangeToSum)

 

[chipsta040365]

Thanks for the reply,

Maybe i need to clarify a little bit.

This spreadsheet is over 18000 rows.

I use the autofilter feature to reduce down to specifics

What i need is a hidden or frozen Cell location that i can put the Autosum Calculation.

I want this to be able to auto calculate the sums of the various Sorts.

i.e. category hats = 10000 for the whole spreadsheet.
i want the total to autosum with each selection
i.e. i auto filter on one rep, he has only 4 rows of the 10000 and i only want the 4 rows which would total 4 X cost.

Does this sound like it can be done?

 

[Andy B]

Aladin gave you the correct formula, replace 'RangeToSum' with your data range for the column you are totalling. i.e. if your data in column B is in B2 to B18500 the formula would be:

Subtotal(9,B2:B18500)

This will only add the visible cells after any filter is applied.

 

[chipsta040365]

Thanks Andy.

I figured out the correct formula and put it 5 lines below bottom.

I did not realize that the filtered rows would not add.

I was trying to put the calculation in another Column, and those numbers were way Off.

Putting the Calculation at the bottom Appears to be working Well.

Thanks for all the help everyone.