Subtotal in a filtered table

Topic

New Reply

Hi All,
I am using ‘=SUM(IF(FREQUENCY(MATCH(J2:J5592,J2:J5592,0),MATCH(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

Reply | Quote

Replies

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

Reply | Quote

I am looking for a subtotal of the filtered, unique values, not a sum re subtotal(9,

More ideas? Thanks.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

I am using Excel2007 .xlsx

So was I. 🙂

Reply | Quote

Any suggestions on how to overcome the error?

Reply | Quote

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

Reply | Quote

Still get the same error 🙁

Reply | Quote

Try the attached file.

Reply | Quote

Try the attached file.

Hello Rory – Would this formula give correct counts for Column D of your example?
=SUBTOTAL(3,D2:D35)
replace the smiley with a colon.
Thanks

Reply | Quote

Hello Rory – Would this formula give correct counts for Column D of your example?
=SUBTOTAL(3,D2:D35)
replace the smiley with a colon.
Thanks

No, because we want a count of distinct records, not just a count of all the records (e.g. 12 rows of ‘b’ counts as 1, not 12)

Reply | Quote

No, because we want a count of distinct records, not just a count of all the records (e.g. 12 rows of ‘b’ counts as 1, not 12)

Would correct counts be calculated using an Advanced Filter “unique” of Column J along with this formula….
=SUBTOTAL(3,J2:J35)
replace the smiley with a colon

PS: Why does the smiley appear in this board’s software?

Reply | Quote

No, because we want a count of distinct records, not just a count of all the records (e.g. 12 rows of ‘b’ counts as 1, not 12)

Would correct counts be calculated using an Advanced Filter “unique” of Column J along with this formula….
=SUBTOTAL(3,J2:J35)

Edit: In Excel 2003 I get a count of 5.

Reply | Quote

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! 🙂

Reply | Quote

No problem. FYI, if you save from xls to xlsx, you then have to close and reopen the file for it to take effect.

Reply | Quote

Which I just did and of course it works in the original file. Duh…. Sorry, such a newbie error! But again, thanks for you time. Everyone here is the best!
mm

Reply | Quote

Since the OP wants to use autofilters, that’s not really an option (You can’t have both)

The board software seems to interpret : followed by D as a smiley – it’s quite common. 😉

Reply | Quote

Rory – Thanks for the explanations.

Reply | Quote