# Thread: Subtotal in a filtered table

1. ## 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. 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. I am looking for a subtotal of the filtered, unique values, not a sum re subtotal(9,

More ideas? Thanks.

4. Originally Posted by Myers515
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. Originally Posted by tfspry
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. 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

PS: I used Advanced Filter. Are you using Auto Filter or Advanced Filter?

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

8. 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. Originally Posted by Myers515
I am using Excel2007 .xlsx
So was I.

10. Any suggestions on how to overcome the error?

11. 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))`

12. Still get the same error

13. Try the attached file.

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

Myers515 (2011-02-03)

15. Originally Posted by rory
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. 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!

Page 1 of 2 12 Last

#### Posting Permissions

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