Filters, Subtotal & Intacted Results after the filters' Removal

Discussion in 'Microsoft Excel Misc' started by kasiopi, Feb 23, 2006.

  1. kasiopi

    kasiopi Guest

    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?action=getinfo&userid=31848
    View this thread: http://www.excelforum.com/showthread.php?threadid=515704
     
    kasiopi, Feb 23, 2006
    #1
    1. Advertisements

  2. kasiopi

    Guest Guest

    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?action=getinfo&userid=31848
    > View this thread: http://www.excelforum.com/showthread.php?threadid=515704
    >
    >
     
    Guest, Feb 23, 2006
    #2
    1. Advertisements

  3. kasiopi

    kasiopi Guest

    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?action=getinfo&userid=31848
    View this thread: http://www.excelforum.com/showthread.php?threadid=515704
     
    kasiopi, Feb 23, 2006
    #3
  4. 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?action=getinfo&userid=31848
    > View this thread: http://www.excelforum.com/showthread.php?threadid=515704[/color]

    --

    Dave Peterson
     
    Dave Peterson, Feb 23, 2006
    #4
  5. kasiopi

    kasiopi Guest

    kasiopi, Feb 24, 2006
    #5
  6. =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

    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?action=getinfo&userid=31848
    > View this thread: http://www.excelforum.com/showthread.php?threadid=515704


    --

    Dave Peterson
     
    Dave Peterson, Feb 24, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    612
    Dave Peterson
    Jun 28, 2006
  2. Guest
    Replies:
    1
    Views:
    409
    Guest
    Oct 12, 2006
  3. Guest

    How do I Subtotal, then Sort by Subtotal Amounts?

    Guest, Oct 28, 2006, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    724
    Guest
    Oct 29, 2006
  4. Guest
    Replies:
    1
    Views:
    493
    Guest
    Mar 15, 2007
  5. GBO

    macro excel subtotal in subtotal

    GBO, Nov 29, 2007, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    496
  6. learningaccess

    subtotal within subtotal

    learningaccess, Aug 28, 2008, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    451
    Dave Peterson
    Nov 3, 2008
  7. David

    sort macro, subtotal and add lines after subtotal

    David, Aug 27, 2009, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    903
  8. Tickfarmer

    Subtotal To Include Item Description On Subtotal Line

    Tickfarmer, Feb 23, 2010, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    582
    Tickfarmer
    Feb 23, 2010
Loading...