Skip to main content

How to count cells/records by group in Excel?

Author: Kelly Last Modified: 2023-12-29

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:
    1. 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.)
    2. With the copied Country column selected, click Data > Remove Duplicates.

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

  1. 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.
  2. 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!

  1. Copy the entire table to a new location where you plan to display the results of the counting and calculations.
  2. Select any cell within the copied table, and click Kutools > Merge & Split > Advanced Combine Rows.
  3. In the pop-up Advanced Combine Rows dialog box:
    1. Select the Country column (the one you'll use for grouping), and select the Primary Key option from the Operation list.
    2. Select the Sales Person column (where you want to count the number of items), and select Count from the Operation list.
    3. Select the Amount column (where you want to perform calculations), and select Sum (or any other calculation options you want) from the Operation list.
    4. 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations