Obtaining a count of items in a column

Copper Contributor

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

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

Hi

 

Use COUNIF() or COUNTIFS().

 

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!

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,”>=“&DuplicateList)=MAX(INDEX(
COUNTIF(DuplicateList,”>=“&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.
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.