LinkBack URL
About LinkBacks
Sorry not at work so can't upload the file however I need a formula to count the unique values in a filtered column. The column has account names and a few are listed more than once. The filtered data is in column A (A2:A747).
Thanks!
Sorry not at work so can't upload the file however I need a formula to count the unique values in a filtered column. The column has account names and a few are listed more than once. The filtered data is in column A (A2:A747).
Thanks!
You can try this:
=SUMPRODUCT(1/COUNTIF($A$2:$A$747,$A$2:$A$747))
- Moo
This gives me 225 because I believe it counts the data from the entire data set. (A2:A747 is filtered so I need a formula that includes subtotal I believe.
Thanks
Hi rabustam04,
See if this works for you.
Column B is the helper column, and C1 contains the result you want.
Don't forget to click the little star to the left of this post if you feel I helped!
Taming the Excel dragon... www.TheExcelphile.com
Sorry. Looks like I'll be learning something new today, too! (if someone else replies with the correct solution).
- Moo
Using your method I get 39 unique values (my data). If I copy and paste special (values) and use the following formula I get 49 unique.
=SUMPRODUCT(1/COUNTIF(A2:A113,A2:A113))
can I use this formula in the filtered data? If I use this formula I get 225 because it counts all the columns
Last edited by rabustam04; 11-27-2012 at 12:25 PM.
Asumming that your data start in A2(A1=title and use auto filter in this), try this Array(confirm using Control+shift+enter-not just enter)
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),MATCH(A2:A100,A2:A100,0)),MATCH(A2:A100,A2:A100,0)),1))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Formula works!
Last edited by rabustam04; 11-27-2012 at 12:27 PM.
This formula works. Thanks!Originally Posted by Fotis1991
Welcome!
Thanks for the reb*
use this variation of Fotis's formula, the performance is better:
Please Login or Register to view this content.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Tried it and works. Whats the difference?use this variation of Fotis's formula, the performance is better:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks