Forum Discussion
Obtaining a count of items in a column
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? ...
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.
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.