How to count cells/records by group in Excel?
 |
Have you encountered the need to tally items or records grouped by certain criteria in a dataset? Observe the table on the left, which displays a list of various countries along with additional data, such as individual names and their corresponding sales figures. Your objective is to calculate the total number of individuals from each country, as illustrated below.
This is a typical situation in data analysis and reporting. Excel, renowned for its powerful and flexible capabilities, provides efficient methods to accomplish this task. Moreover, in this tutorial, we will guide you through a straightforward approach to perform calculations on the sales amounts, organized by country. |
Count cells/records by group with COUNTIF function
For our first method, we'll delve into a fundamental technique for counting people based on their countries. This process involves utilizing Excel's UNIQUE function or Remove Duplicates utility, combined with the COUNTIF function. Please do as follows:
Step 1: Get unique countries
- If you are using Excel for Microsoft 365, Excel 2021, or newer versions, you can quickly list all unique countries with a simple UNIQUE formula. Enter the following formula where you want to display the unique countries:
=UNIQUE(A2:A24)Tip: The range A2:A24 refers to the column containing countries from which you want to extract unique values.
- For those using earlier versions of Excel, the Remove Duplicates utility is your solution for obtaining unique countries:
- Copy the Country column (which you will count by), and paste it to the desired location for unique countries. (Here I pasted it to the column E.)
- With the copied Country column selected, click Data > Remove Duplicates.
- In the Remove Duplicates dialog that appears, click OK.
Tip: A dialog will then inform you how many duplicates have been removed. Click OK again to close this dialog.
Step 2: Use the COUNTIF function to count salespeople based on the unique countries
- Select the blank cell beside the unique country list, in this case, cell F2, and enter the below formula.
=COUNTIF($A$1:$A$24,E2)Tip: The formula is used to count how many times the value in cell E2 appears in the range $A$1:$A$24. The dollar signs ($) fix the range, ensuring it remains constant when the formula is copied to other cells. - Double-click on the fill handle (the small green square in the lower-right corner) of the result cell to quickly count salespeople for all countries.
Count cells/records by group and calculate related data with Kutools for Excel
The second method utilizes Kutools for Excel's Advanced Combine Rows utility to ease your counting and calculation tasks. This powerful feature enables you to batch count and calculate data by groups swiftly with only a few clicks. Follow the steps below to get started:
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
- Copy the entire table to a new location where you plan to display the results of the counting and calculations.
- Select any cell within the copied table, and click Kutools > Merge & Split > Advanced Combine Rows.
- In the pop-up Advanced Combine Rows dialog box:
- Select the Country column (the one you'll use for grouping), and select the Primary Key option from the Operation list.
- Select the Sales Person column (where you want to count the number of items), and select Count from the Operation list.
- Select the Amount column (where you want to perform calculations), and select Sum (or any other calculation options you want) from the Operation list.
- Once done, click OK.
As a result, the table will reorganize, merging rows based on unique countries. It will display the number of salespeople and the total sales amount for each country, as configured.
Note: Want to access the Advanced Combine Rows feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!
Related articles:
Calculate average by year/month/date in Excel
Calculate weekly average in Excel
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!
