Google Answers Logo
View Question
Q: Excel Formula question (counting unique values) ( Answered 5 out of 5 stars,   0 Comments )
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:


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:5 out of 5 stars
Hi dagnyt,

Here's an example of a formula that will do this:


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:

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.


dagnyt-ga rated this answer:5 out of 5 stars
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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy