Filters, Subtotal & Intacted Results after the filters' Removal
 

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..


--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704

Filters, Subtotal & Intacted Results after the filters' Removal
 

Hi,

You can try with Pivot Tables. It works best.




Warm regards
NAVEEN

"kasiopi" wrote:


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..


--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704

Filters, Subtotal & Intacted Results after the filters' Removal
 

NAVEEN Wrote:
Hi,
You can try with Pivot Tables. It works best.
[/color]

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?


--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704

Filters, Subtotal & Intacted Results after the filters' Removal
 

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"))




kasiopi wrote:

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


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?

--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704[/color]

--

Dave Peterson

Filters, Subtotal & Intacted Results after the filters' Removal
 

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?


--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704

Filters, Subtotal & Intacted Results after the filters' Removal
 

=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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

kasiopi wrote:

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?

--
kasiopi
------------------------------------------------------------------------
kasiopi's Profile: http://www.excelforum.com/member.php...o&userid=31848
View this thread: http://www.excelforum.com/showthread...hreadid=515704

--

Dave Peterson