In this blog post I will explain 3 different methods to find the Count of each Item in the list shown below. The methods include the use Pivot Table, Power Query and Formulas.
Note that I have converted the list into an official Excel Table and named it as ABCD.
Table of Contents
Dynamic Array Formula to find the count of each Item
When the Table ABCD is supplied into the UNIQUE function, it will return an array of distinct values in the list.
=UNIQUE(ABCD[Items])
The same formula when used inside COUNTIF function will return the Count of each item.
=COUNTIF(ABCD[Items],UNIQUE(ABCD[Items]))
The above formula is then combined with the UNIQUE function.
=UNIQUE(ABCD67[Items])&" - "&COUNTIF(ABCD67[Items],UNIQUE(ABCD67[Items]))
Add some data to the list to see the ‘Dynamic’ nature of the formula.
Find the Count using Pivot Table
As you may know PivotTable is best tool in Excel to slice and dice data.
To create a Pivot Table from the list, Select the table > go to the Table Design tab in the Excel ribbon > Summarize with PivotTable > a dialog related to PivotTable will be activated > Specify the cell where you want to place the PivotTable > Click on OK
Drag and drop the field called Items into the area for Rows to create a distinct list of items.
Drag and drop the field called Items into the area for Values and we will have the Count of each item.
Find the Count using Power Query
To load the Table into the Power Query Editor of Excel, Select the Table > go to the Data of the Excel Ribbon > Click on From Selection in the category called Get & Transform Data
Once the Table is loaded in the Power Query Editor of Excel, Click on Group By in the Home tab.
A dialog called Group By will be activated. Specify a name for the new Column to be created > select Count Rows for Operation and click on OK
A new column with count of the Items is created.
To load the data into the Excel worksheet, Click on Close and Load > Close and Load to,
A dialog called Import Data will be activated. Use this dialog to specify the cell where you want to place the table created using Power Query.
The new table with count of Items is loaded into the Excel worksheet.
Every method explained above are of dynamic nature. So, let’s add a few items to the list and check the results.
