Counting occurrances of a value with a Column

Posted by Nev on September 06, 2000 8:42 AM

Without using a pivot table how can a count the number of unique string entrys within a column. i.e.

aa
aa
bb
bb
cc
cc

Would = 3 (Only 3 occurrancs of the same string)

Thanks

Posted by Tim Francis-Wright on September 06, 0100 11:14 AM

Chip Pearson's page has some good formulas to help with this:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

In general, you can use the following array
formula: (use control-shift-enter to enter it)
=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

If there are no blanks, you can use the
following array formula:
=SUM(1/COUNTIF(Range1,Range1))

Of course, substitute your range for Range1
in either formula.

HTH

Posted by Neville Collins on September 06, 0100 11:39 AM

Thats great thanks. But what is ctrl+shift+enter actually doing??

Regards

Posted by Tim Francis-Wright on September 06, 0100 1:25 PM

Control-shift-enter tells Excel that you're
entering an array formula--something that passes
a whole range to a function that normally
takes only one cell as an argument. For example,
in the first formula, the C-S-E entry forces
Excel to pass each cell in Range1 through the
formula.

Check out the Mr. Excel tip archive for more
about this.

HTH

Posted by Doug on September 08, 0100 9:11 PM

Assuming your data is sorted you can add a formula to an adjacent column (=if(A1=A2,1,0)), you can fill this formula down adjacent to your data and then sum the column. Obviously this just counts anonymous recurrences (may have 3 value "bb" and 2 value "CC").

I use this technique to isolate an individual list of values in a list of multiple recurring values by using COPY - PASTE SPECIAL (VALUES) and then sorting by the results of the IF statement.

Hope this helps
Doug