Obtaining a count of items in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-362962%22%20slang%3D%22en-US%22%3EObtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362962%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20sheet%20that%20has%2038%2C074%20company%20names%20in%20a%20column%2C%20of%20which%202%2C512%20are%20unique.%26nbsp%3B%20How%20can%20I%20determine%20the%20number%20of%20times%20each%20of%20the%202%2C512%20appear%20in%20order%20to%20make%20the%20total%20of%2038%2C074%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-362962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363326%22%20slang%3D%22en-US%22%3ERe%3A%20Obtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363326%22%20slang%3D%22en-US%22%3EO%20yes%2C%20you%20definitely%20will%20need%20a%20non-DA%20installation%20of%20Excel%20for%20the%20upcoming%20period%2C%20until%20all%20of%20your%20clients%20have%20upgraded.%20Virtual%20Machines%20(or%20multiple%20physical%20computers)%20are%20very%20useful%20for%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363061%22%20slang%3D%22en-US%22%3ERe%3A%20Obtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363061%22%20slang%3D%22en-US%22%3EHello%20Ryan%2C%3CBR%20%2F%3EAssuming%20the%20labels%20in%20A1%3AC1%20are%20Duplicate%2C%20Distinct%2C%20and%20Count.%20I%20suggest%20these%20steps%20for%20backward%20compatibility.%3CBR%20%2F%3EFirst%2C%20define%20the%20company%20names%20as%20DuplicateList%20with%20this%20formula%3A%3CBR%20%2F%3E%24A%242%3AINDEX(%24A%3A%24A%2CCOUNTA(%24A%3A%24A))%3CBR%20%2F%3ESecond%2C%20extract%20the%20distinct%20company%20names%2C%20sorted%20in%20ascending%20order%2C%20starting%20in%20B2%20with%20this%20formula%3A%3CBR%20%2F%3E%3DLOOKUP(PI()%2C%3CBR%20%2F%3E1%2F(COUNTIF(DuplicateList%2C%E2%80%9D%26gt%3B%3D%E2%80%9C%26amp%3BDuplicateList)%3DMAX(INDEX(%3CBR%20%2F%3ECOUNTIF(DuplicateList%2C%E2%80%9D%26gt%3B%3D%E2%80%9C%26amp%3BDuplicateList)*(COUNTIF(B%241%3AB1%2CDuplicateList)%3D0)%2C0)))%2C%3CBR%20%2F%3EDuplicateList)%3CBR%20%2F%3EThird%2C%20count%20the%20instances%20of%20each%20item%20in%20the%20Distinct%20List%20in%20the%20DuplicateList%2C%20starting%20in%20C2%20with%20this%20formula%3A%3CBR%20%2F%3E%3DCOUNTIF(DuplicateList%2CB2)%3CBR%20%2F%3EConsequently%2C%20there%20will%20be%202%2C512%20distinct%20company%20names%20in%20Column%20B.%20Also%2C%20the%20sum%20of%20the%20counts%20in%20Column%20C%20will%20be%2038%2C074.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363017%22%20slang%3D%22en-US%22%3ERe%3A%20Obtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363017%22%20slang%3D%22en-US%22%3E%3CP%3EI%20loaded%20the%20dynamic%20array%20version%20of%20Excel%20and%20already%20I%20am%20dreading%20having%20to%20revert%20to%20deliver%20client%20workbooks.%26nbsp%3B%20I%20made%20the%20list%20of%20companies%20into%20a%20table%20so%20that%20the%20named%20range%20'list'%20(of%20companies)%20becomes%20dynamic.%26nbsp%3B%20I%20then%20move%20to%20my%20formula%20cell%20and%20named%20it%20'distinct'.%3C%2FP%3E%3CP%3EThe%20formula%20I%20inserted%20into%20the%20cell%20is%3C%2FP%3E%3CP%3E%3D%20SORT(UNIQUE(list))%3C%2FP%3E%3CP%3Ewhich%20spills%20to%20provide%20a%20list%20of%20the%20distinct%20company%20names%20from%20'list'%2C%20sorted%20alphabetically.%26nbsp%3B%20Moving%20across%20again%2C%20I%20name%20a%20new%20formula%20cell%20'occurrences'%20and%20then%20insert%20the%20formula%3C%2FP%3E%3CP%3E%3D%20COUNTIFS(list%2C%20distinct%23)%3C%2FP%3E%3CP%3Eto%20get%20a%20count%20corresponding%20to%20each%20distinct%20company.%3C%2FP%3E%3CP%3EThe%20number%20of%20distinct%20companies%20'countDistinct'%20is%20given%20by%3C%2FP%3E%3CP%3E%3D%20COUNTA(distinct%23)%3C%2FP%3E%3CP%3Eand%2C%20just%20as%20a%20check%2C%20the%20values%3C%2FP%3E%3CP%3E%3D%20COUNTA(list)%3C%2FP%3E%3CP%3E%3D%20SUM(occurrences%23)%3C%2FP%3E%3CP%3Eare%20identical.%3C%2FP%3E%3CP%3ESo%20few%20formulas%2C%20so%20many%20results!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362978%22%20slang%3D%22en-US%22%3ERe%3A%20Obtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362978%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20COUNIF()%20or%20COUNTIFS().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362974%22%20slang%3D%22en-US%22%3ERe%3A%20Obtaining%20a%20count%20of%20items%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362974%22%20slang%3D%22en-US%22%3EFormat%20your%20data%20as%20a%20table%20and%20then%20click%20Insert%2C%20Pivot%20table.%20Drag%20company%20name%20to%20the%20row%20area%20and%20to%20the%20sigma%20area.%3C%2FLINGO-BODY%3E
Occasional Visitor

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
Highlighted
Format your data as a table and then click Insert, Pivot table. Drag company name to the row area and to the sigma area.
Highlighted

Hi

 

Use COUNIF() or COUNTIFS().

 

Highlighted

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!

Highlighted
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.
Highlighted
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.