Count in Excel

Colin Legg · #4 06-09-2011, 10:53 AM

That means you want to count the distinct items, not the unique ones.

There are several formulas which can be used to do this, but none of them are simple. The most commonly used one, as outlined here, is:

Code:

=SUM(IF(FREQUENCY(IF(A1:A100<>"",MATCH("~"&A1:A100,A1:A100&"",0)),ROW(A1:A100)-ROW(A1)+1),1))

This is an array formula so when you type it into the formula bar you have to complete the entry with CTRL+SHIFT+ENTER, not just ENTER. You will also have to adjust the A1:A100 references to the range containing your names, and A1 to the first cell in the range containing your names.

If you prefer I can direct you to a VBA UDF which will make the formula simpler.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count with multiple conditions	bundy5150	Excel	4	02-22-2011 10:00 AM
Don't count hidden slides	Lu_Argentina	PowerPoint	0	10-12-2010 02:12 PM
How to Count from Different Cells?	Shahzad	Excel	3	06-26-2010 01:06 AM
changing word count	hamster	Word	3	06-03-2010 01:53 AM
Don't count 0 in a chart	Rmaisone	Excel	0	08-21-2006 12:26 PM