Function Problems

How to Count Selected Country Codes and Group Others in Spreadsheets

Partner Editor Oct 1, 2026 868 views

Question details

The user needs to count specific country codes individually and calculate the total of all unselected country codes to place them in a single "Other" category.

How to Count Selected Country Codes and Group All Others as 'Other'

Product: Spreadsheets
Device & OS: not provided
Scenario: Categorizing and summarizing location data where primary countries need specific counts and the remaining miscellaneous countries need to be grouped together.
Observed behavior: Data counting requires separate inclusion criteria for selected countries and a multiple-exclusion formula for the remaining countries, with adjustments needed depending on data formatting.

Before you start

Verify how your data is formatted. If multiple country codes are combined in a single cell (e.g., separated by commas), you must split them into individual cells before applying any counting formulas.

Solution 1Recommended

Use COUNTIF and COUNTIFS Formulas

Apply the COUNTIF function to count your specific target country codes and use COUNTIFS with exclusion operators to group the remaining codes into an 'Other' category.

This method assumes your country codes are listed vertically in a single column (e.g., Column A). It utilizes standard logical operators to filter the data.

Count the specific country code

Select an empty cell where you want to display the total for your specific country. Type the formula =COUNTIF(A:A, "cn") (replace 'cn' with your desired country code and A:A with your actual data range) and press Enter.

Prepare the 'Other' category cell

Select a different empty cell where you want the combined total of all unselected countries to appear.

Apply the COUNTIFS exclusion formula

Enter a formula using the 'not equal to' (<>) operator to exclude the codes you already counted. For example, type =COUNTIFS(A:A, "<>cn", A:A, "<>us") to count every country code except 'cn' and 'us'.

Platform Syntax Differences: If you are using Google Sheets or have specific regional settings in Excel, verify your separator syntax. You may need to use semicolons instead of commas to separate formula arguments.

Solution 2

Split Comma-Separated Data Before Counting

If your country codes are combined as a single text string within one cell, separate them into distinct columns or rows before running your counting functions.

Smart Spreadsheet Formulas

Easily Analyze Data with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced logical functions like COUNTIF and COUNTIFS, helping you effortlessly categorize, group, and analyze your datasets without compatibility issues.

1. Format Data: Open your dataset in WPS Spreadsheet and use the Data tab to split any comma-separated strings into individual cells.
2. Count Target Data: Use the =COUNTIF() formula to easily calculate totals for your priority country codes.
3. Group Remaining Data: Use the =COUNTIFS() formula combined with the <> (not equal to) operator to dynamically group and count all remaining countries as 'Other'.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Built-in 'Text to Columns' tool for fast data preparation and cleaning.Lightweight software that quickly processes large datasets.User-friendly interface matching traditional spreadsheet layouts.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS exclusion formula returning an error in Google Sheets?

Google Sheets might require different syntax based on your browser's locale settings. Try replacing the commas in your formula with semicolons, and double-check that your exclusion parameters (like "<>cn") are correctly enclosed in standard double quotation marks.

How can I group three or more countries into the 'Other' category?

You can add as many exclusion conditions as needed within the COUNTIFS function. Just repeat the range and the exclusion criteria. For example: =COUNTIFS(A:A, "<>cn", A:A, "<>us", A:A, "<>uk", A:A, "<>fr").

What if my country codes have extra spaces in the cells?

Extra spaces will cause exact match formulas like COUNTIF to fail. Before calculating, use the TRIM function or the Find and Replace tool to remove any accidental leading or trailing spaces from your data.

Excel & Data Problems