How to count data by group in Excel?
In Excel, counting the total number of values in a simple range is a common and straightforward task for most users. However, in many real-life scenarios—such as sales reports, inventory lists, or employee data—information is often organized into groups, like product categories, departments, or regions. In these cases, you might need to not only count the overall total, but also determine the number of occurrences within each group. This requirement is especially common for summarizing and analyzing grouped data for reports or making data-driven decisions. While there is no single direct button or built-in function explicitly labeled "Count by Group" in Excel, several reliable and efficient methods are available to achieve this goal.
Below are practical methods you can use to count the data in each group in Excel, catering to different user needs and levels—from built-in features to versatile formula-based and automated VBA solutions. Each method comes with its own advantages, making it easy to choose the approach most suitable for your particular worksheet scenario.
Count data by group with a pivot table
Count data by group with VBA code
Count data by group with Excel formulas (COUNTIF/COUNTIFS)
Count data by group with a pivot table
Pivottables are ideal for quickly summarizing large data sets by different categories, including counting items in each group. For instance, if you have a long list of sales transactions and want to know how many times each product was sold, a pivot table is one of the quickest practical tools to use.
Suppose you have a dataset like the example below and want to count the number of occurrences by group (such as counting how many times each item or category appears):
1. Select the entire data range that includes the groups and data you want to count. Click Insert > Pivot Table > Pivot Table in the Excel ribbon. See screenshot:
2. In the Create Pivot Table dialog, choose whether to place the pivot table in a new worksheet or in the existing worksheet. If you select Existing Worksheet, be sure to pick an empty cell that will not interfere with your existing data. See screenshot:
3. Click OK. On the right side of the Excel window, the PivotTable Field List pane appears. Drag the group column header (for example, "Item" or "Category") to both the Row Labels and the Values sections. By default, the values area will use a “count” function, displaying how many times each entry appears. See screenshot:
You will instantly see a report where Excel has grouped your data and shown the count for each group. This is especially helpful for visualizing the distribution of items, products, or records across categories. If you update your original dataset, remember to refresh the pivot table to reflect any changes.
Tips and Notes: If your data source range includes blank rows or columns, make sure to exclude them when creating the pivot table, as it may affect the accuracy of your grouping. Pivot tables provide an intuitive way to summarize data, but if you need to reuse results elsewhere in formulas or across sheets, or want more flexible reporting, consider the formula or VBA solutions below.
Count data by group with VBA code
If your workbook requires frequent counting and reporting on differently grouped data, or you need to automate this process for large data sets or recurring tasks, you can utilize a VBA script. VBA (Visual Basic for Applications) allows for customizing and automating Excel processes, enabling you to create reports, summarize information, or export group counts with minimal manual intervention.
Precaution: Always save your work before running any new VBA code. VBA enables powerful automation but may overwrite data if not configured carefully.
1. Go to Developer tab > Visual Basic to open the VBA editor. In the window that appears, click Insert > Module, and paste the following code into the module window:
Sub GroupCount()
Dim dict As Object
Dim lastRow As Long
Dim groupCol As Range
Dim groupCell As Range
Dim outputRow As Long
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Change Sheet1 and column as needed
With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set groupCol = .Range("A2:A" & lastRow)
For Each groupCell In groupCol
If Not dict.Exists(groupCell.Value) Then
dict(groupCell.Value) = 1
Else
dict(groupCell.Value) = dict(groupCell.Value) + 1
End If
Next groupCell
outputRow = 2
.Cells(1, "C").Value = "Group"
.Cells(1, "D").Value = "Count"
For Each key In dict.Keys
.Cells(outputRow, "C").Value = key
.Cells(outputRow, "D").Value = dict(key)
outputRow = outputRow + 1
Next key
End With
End Sub2. To execute the code, press F5 or click the 
"Run" button while in the VBA editor. The script will scan the group data in column A (starting from A2) on "Sheet1", tally up counts for each group, and output the summarized result in columns C and D starting from row 2.
Notes: You can modify the "Sheet1", column references and output locations as needed for your specific workbook. If your data contains empty cells or special cases, review the results to ensure accuracy. If duplicate group names have different spellings (e.g., "Apple" vs. "apple"), results will treat these as separate groups. For customized grouping (case-insensitive, sorted output, or more complex groupings), further additions to the VBA code may be required.
VBA is best suited for automated, repeatable tasks—especially when dealing with large or frequently updated datasets where manual summarization is time-consuming. If you encounter errors like "Object variable not set" or "Subscript out of range," confirm that your sheet and range references match your actual data structure.
Count data by group with Excel formulas (COUNTIF/COUNTIFS)
For users who prefer working directly on worksheet grids or need dynamic, formula-based solutions for further calculation and referencing, Excel’s COUNTIF and COUNTIFS functions provide an effective workaround. These formulas are particularly useful when you want group counts to update automatically as your underlying data changes, or if you need results right next to your data for reference or further analysis.
Example scenario: Suppose your data is in columns A (Group Name) and B (Value), and you wish to count how many times each group appears.
1. In a new column, next to your data (e.g., cell C2), input the formula below to calculate the count for each group:
=COUNTIF($A$2:$A$100, A2)2. After entering the formula, press Enter. To apply this formula to all rows, drag the fill handle down from cell C2 to fill the cells alongside your data, or double-click the fill handle to auto-fill. The formula will return the number of occurrences for the group in that row.
3. If you want to get a unique list of all groups and their corresponding counts, first extract distinct group names (for example, by using the Remove Duplicates feature or a UNIQUE formula, depending on your Excel version) and then apply the COUNTIF formula to the unique list.
Parameter explanations: In the above formula, $A$2:$A$100 is the range containing your group names. Adjust this range to match your actual data. A2 is the cell reference for the current row's group value.
This formula solution is highly flexible: you can use it for filtered lists, sorted data, or alongside other calculations. However, be mindful of performance if applied to very large datasets, as recalculation may take additional time.
COUNTIFS allows for counting by multiple criteria if your grouping is more complex (e.g., grouping by both category and region).
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in