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, Feb 23, 2006
    #1
    1. Advertisements

  2. kasiopi

    Guest Guest

    Hi,

    You can try with Pivot Tables. It works best.




    Warm regards
    NAVEEN
     
    Guest, Feb 23, 2006
    #2
    1. Advertisements

  3. kasiopi

    kasiopi Guest

    [/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?
     
    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"))




    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]
     
    Dave Peterson, Feb 23, 2006
    #4
  5. kasiopi

    kasiopi Guest

    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, Feb 24, 2006
    #5
  6. Dave Peterson, Feb 24, 2006
    #6
    1. Advertisements

Ask a Question

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.