Obtaining a count of items in a column

Question

I am working on a sheet that has 38,074 company names in a column, of which 2,512 are unique.&nbsp; How can I determine the number of times each of the 2,512 appear in order to make the total of 38,074?&nbsp; &nbsp;

twifoo · Answer

Hello Ryan,Assuming the labels in A1:C1 are Duplicate, Distinct, and Count. I suggest these steps for backward compatibility.First, define the company names as DuplicateList with this formula:$A$2:INDEX($A:$A,COUNTA($A:$A))Second, extract the distinct company names, sorted in ascending order, starting in B2 with this formula:=LOOKUP(PI(),1/(COUNTIF(DuplicateList,”&gt;=“&amp;DuplicateList)=MAX(INDEX(COUNTIF(DuplicateList,”&gt;=“&amp;DuplicateList)*(COUNTIF(B$1:B1,DuplicateList)=0),0))),DuplicateList)Third, count the instances of each item in the Distinct List in the DuplicateList, starting in C2 with this formula:=COUNTIF(DuplicateList,B2)Consequently, there will be 2,512 distinct company names in Column B. Also, the sum of the counts in Column C will be 38,074.

jkpieterse · Answer

O yes, you definitely will need a non-DA installation of Excel for the upcoming period, until all of your clients have upgraded. Virtual Machines (or multiple physical computers) are very useful for this.

jkpieterse · Answer

Format your data as a table and then click Insert, Pivot table. Drag company name to the row area and to the sigma area.

detlef_lewin · Answer

Hi&nbsp;Use COUNIF() or COUNTIFS().&nbsp;

peterbartholomew1 · Answer

I loaded the dynamic array version of Excel and already I am dreading having to revert to deliver client workbooks. I made the list of companies into a table so that the named range 'list' (of companies) becomes dynamic. I then move to my formula cell and named it 'distinct'.

The formula I inserted into the cell is

= SORT(UNIQUE(list))

which spills to provide a list of the distinct company names from 'list', sorted alphabetically. Moving across again, I name a new formula cell 'occurrences' and then insert the formula

= COUNTIFS(list, distinct#)

to get a count corresponding to each distinct company.

The number of distinct companies 'countDistinct' is given by

= COUNTA(distinct#)

and, just as a check, the values

= COUNTA(list)

= SUM(occurrences#)

are identical.

So few formulas, so many results!