View Question
Q: Excel Formula question (counting unique values) ( Answered ,   0 Comments )
 Question
 Subject: Excel Formula question (counting unique values) Category: Computers > Software Asked by: dagnyt-ga List Price: \$3.00 Posted: 06 Jul 2005 17:16 PDT Expires: 05 Aug 2005 17:16 PDT Question ID: 540724
 ```Excel question: Would like to count the total number of UNIQUE text and number values, but NOT BLANKS in a column of data, using an excel formula. For example if my list looks as follows: Con.01 Con.03 REC REC Con.04 Con.01 (blank) Ord.01 I would want the formula to return the number 5 (counting con.1, con.3, rec, con.4, ord.1).```
 Subject: Re: Excel Formula question (counting unique values) Answered By: efn-ga on 06 Jul 2005 19:34 PDT Rated:
 ```Hi dagnyt, Here's an example of a formula that will do this: =SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1)) You would have to replace all the occurrences of "A1:A10" with a specification of the range you want to count. You will also have to enter this formula as an array formula. To do that, press Ctrl-Shift-Enter to enter it. If you paste the text into a cell, you can convert it to an array formula by editing the cell (with function key F2, for example), then ending the edit with Ctrl-Shift-Enter. An array formula appears enclosed in braces ({}), but typing the braces yourself does not make a formula an array formula--you have to do the Ctrl-Shift-Enter bit. I found this formula on this Microsoft Knowledge Base page: http://support.microsoft.com/kb/q268001/ I had searched for "excel count unique" (without the quote marks). I tried the formula out with your data in Microsoft Excel 2000 and it worked. If you need any more help with this, please ask for a clarification. Regards, --efn```
 dagnyt-ga rated this answer: ```Thank you! The excel help included with the software gave only two of the examples, neither of which fit my situation (blank cells and text) and I wasn't familiar enough with the functions to derive my own. Much appreciated and thanks for the reference link!```

 There are no comments at this time.