Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    Subtotal in a filtered table

    Hi All,
    I am using '=SUM(IF(FREQUENCY(MATCH(J2:J5592,J2:J5592,0),MATC H(J2:J5592,J2:J5592,0))>0,1)) to get the unique count of items in my data which is formatted as a table.
    The above will not give me a correct unique count when the spreadsheet is filtered (value remains the same). Does anyone have a suggestion on changes to the above statement or a different approach?
    Thanks
    mm

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can't you just select the summation symbol on the top and hit enter. If you look at the calculation, it uses =subtotal(9,c2:C100) for example

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    I am looking for a subtotal of the filtered, unique values, not a sum re subtotal(9,

    More ideas? Thanks.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Myers515 View Post
    I am looking for a subtotal of the filtered, unique values, not a sum re subtotal(9,
    Once you have the unique list in another location, can you just use the COUNT function to count the values in the list?
    Or filter the list in place and use the SUBTOTAL(2,range) function. Please note that the 2 in SUBTOTAL function returns a count of the values.

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by tfspry View Post
    Once you have the unique list in another location, can you just use the COUNT function to count the values in the list?
    Or filter the list in place and use the SUBTOTAL(2,range) function. Please note that the 2 in SUBTOTAL function returns a count of the values.
    Using the SUBTOTAL (2, range) still does not give me a UNIQUE count of values.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Take a look at example attached.

    Column A is a "Filtered" list of unique values.
    Cell B2 shows a count of the unique values from the Filtered Column A
    Cell C2 shows how many total values unfiltered are in Column A

    Try a "Show All" to see all the values in Column A

    If no joy, please attach a sample of your data.

    PS: I used Advanced Filter. Are you using Auto Filter or Advanced Filter?
    Attached Files Attached Files
    Last edited by tfspry; 2011-02-02 at 15:47. Reason: added a PS

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =SUM(IF(FREQUENCY(MATCH(IF(SUBTOTAL(3,OFFSET(datar ange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),IF(SUBTOTAL (3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),0),MATCH(IF (SUBTOTAL(3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),IF(SUBTOTAL (3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),0))>0,1))-(SUBTOTAL(3,datarange)<>COUNTA(datarange))

    array-entered. Replace datarange with the address in question (i.e. J2:J5592 here, or use a table-reference)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks Rory, however when I try to enter the array formula I get this error: "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."
    I am using Excel2007 .xlsx
    Thanks
    mm

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Myers515 View Post
    I am using Excel2007 .xlsx
    So was I.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Any suggestions on how to overcome the error?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It would appear that the board software has a stupid habit of padding long text. Let's try with code tags:
    Code:
    =SUM(IF(FREQUENCY(MATCH(IF(SUBTOTAL(3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),IF(SUBTOTAL(3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),0),MATCH(IF(SUBTOTAL(3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),IF(SUBTOTAL(3,OFFSET(datarange,ROW(datarange)-MIN(ROW(datarange)),,1)),datarange,""),0))>0,1))-(SUBTOTAL(3,datarange)<>COUNTA(datarange))
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Still get the same error

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try the attached file.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  14. The Following User Says Thank You to rory For This Useful Post:

    Myers515 (2011-02-03)

  15. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by rory View Post
    Try the attached file.
    Hello Rory - Would this formula give correct counts for Column D of your example?
    =SUBTOTAL(3,D235)
    replace the smiley with a colon.
    Thanks

  16. #15
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks Rory,
    I saved your file autofilter.xlsx and did not get the error so...
    Found out that just doing a Save As of my file from .xls to .xlsx is not enough to change it's underlying structure. I created a new .xlsx and copied my data in. The formula works beautifully! Thanks for hanging with me through my trial and error!
    Last edited by Myers515; 2011-02-03 at 10:28. Reason: acknowledgement

Page 1 of 2 12 LastLast

Posting Permissions

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