+ Reply to Thread
Results 1 to 8 of 8

Filters, Subtotal & Intacted Results after the filters' Removal

  1. #1
    Registered User
    Join Date
    02-23-2006
    Posts
    3

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

  2. #2
    NAVEEN
    Guest

    RE: 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
    >
    >


  3. #3
    Registered User
    Join Date
    02-23-2006
    Posts
    3
    Quote Originally Posted by NAVEEN
    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?

  4. #4
    Dave Peterson
    Guest

    Re: 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

  5. #5
    Registered User
    Join Date
    02-23-2006
    Posts
    3
    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?

  6. #6
    Dave Peterson
    Guest

    Re: 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 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...o&userid=31848
    > View this thread: http://www.excelforum.com/showthread...hreadid=515704


    --

    Dave Peterson

  7. #7
    Registered User
    Join Date
    07-15-2019
    Location
    chennai,india
    MS-Off Ver
    2010
    Posts
    1

    Re: Filters, Subtotal & Intacted Results after the filters' Removal

    Hii could you plese tell me how you used sumproduct

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,343

    Re: Filters, Subtotal & Intacted Results after the filters' Removal

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1