Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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


Re: Counting occurrances of a value with a Column

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


Re: Counting occurrances of a value with a Column

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

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

Regards


Re: Counting occurrances of a value with a Column

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


Re: Counting occurrances of a value with a Column

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



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.