Home

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
Ryant818
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
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.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies