Filters, Subtotal & Intacted Results after the filters' Removal

[kasiopi]

Hello.

I have a problem:
I have a data list on which I aplly a filter. In order to count the
results of the filter I use the subtotal function. The problem is that
when I remove the filter the results of the subtotal change, and are
abjusted to the new appearence of the data, which is normal, but not
not what I hoped for. I want this result to be intacked, and
independent of the wheather the folters remains or not.

Can anybody help me???

Thanks in advance..

 

[Guest]

Hi,

You can try with Pivot Tables. It works best.




Warm regards
NAVEEN

 

[kasiopi]

Hi,
You can try with Pivot Tables. It works best.

[/QUOTE]

Are you sure this is the only way?
It is stated to me that in a* specific * cell calculate how many
results are enumerated after the filter is applyed is such way that
when the filter is disabled the outcome remains the same.

With the pivot table, a new table is created.

Am I wrong?

 

[Dave Peterson]

If you want the results of the formula to be the same whether the data is
filtered or not, then you shouldn't use =subtotal(). It ignores those rows
hidden by a filter.

But I have a question.

What value to you want to keep. The value when the filter is applied (and rows
are hidden) or the value that appears when all the rows are visible?

If you want to keep the values when all the rows are visible, then =sum(),
=average(), =count(), ... seem like sensible solutions.

If you want to keep the values when some of the rows are hidden, you can either
convert the formulas to value (edit|copy, edit|paste special|values), or maybe
you could use another column that would indicate that the row should be treated
as visible or hidden.

Then use that in your formula.

Kind of like:
=sumproduct(--(a2:a99="asdf"),--(x2:x99="Visible"))




Are you sure this is the only way?
It is stated to me that in a* specific * cell calculate how many
results are enumerated after the filter is applyed is such way that
when the filter is disabled the outcome remains the same.

With the pivot table, a new table is created.

Am I wrong?
[/QUOTE]

 

[kasiopi]

The sumproduct worked fine!! It is a very clever way of use it as
count!
Thanks very much!

But how did you kwow about this syndax? the double '-' and the
parenthesis?

 

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html