Filtered subtotals

[JulieD]

Hi Govind

not sure if this is the best solution but you can use Data / Subtotals on a
sorted & filtered list - i guess another option would be to use Pivot
Tables.

Cheers
JulieD

 

[Govind]

Hi All,

Is there a way to filter a subtotal.? For eg. lets say i have a list
like this:


Region salesman Manager Amount

X ABC AB 500
Y DEF AB 800
Z GEH GH 200
X DEF GH 700


In this case, i apply a filter to show the sales of Region X only.So a
subtotal of that is 1200.

However i need a further split by manager only for the filtered selection:

Region Manager Amount

X AB 500
X GH 700

Is there a way to do it. I dont want to use sumproduct bcos it takes
into account the cells hidden during filters also.

Regards

Govind.

 

[Govind]

Hi Julie,

Am not in favour of data/subtotals on a filtered list bcos
i need to do this every time i filter the list.

A pivot might be fine, but just want to know if there are
any formulas which can do this trick as well.

regards

Govind

 

[Frank Kabel]

Hi Govind
use SUMPRODUCT with a trick:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1:$A$10)-
ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($B$1:$B$10="Manager_1"))

 

[Govind]

Hi Frank,

That works fine but gives only the count of the Manager for the filtered
list.I need the sum as per the Amount column as well. I tried adding
D110 for including the amount column but it errors out with #Value! error.

Regards

Govind.