How to Use Excel Geography Data Types in COUNTIF Formulas
Question details
The user needs to count Excel Geography data type cells with COUNTIF, but the linked data type stores an object rather than the displayed country name.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has a country column formatted as Excel Geography data types and wants to count how many times each country appears.
- Observed behavior
- COUNTIF does not evaluate the displayed country name correctly because the Geography cell is a linked data type, not plain text.
Select a Geography data type cell and confirm the linked data type icon is visible. Add an empty helper column next to the Geography column so the displayed country name can be extracted before you apply COUNTIF.
Extract the Geography Name property into a helper column and use COUNTIF
Use this when you need a reliable COUNTIF result from existing Geography data type cells.
Geography data types are linked records. The cell shows a country name, but the underlying value is not a string, so COUNTIF cannot match it directly. Extracting the .Name property creates plain text that COUNTIF can evaluate.
Open the Excel workbook and select the first blank cell next to your Geography data type column, for example C2.
Type =A2.Name in C2, where A2 is the Geography data type cell, and press Enter.
Drag the fill handle down the helper column to extract the displayed country name for every row.
Enter =COUNTIF($C$2:$C$100,"United States") or reference a criteria cell, then press Enter.
Use SUMPRODUCT to compare the extracted Geography Name property directly
Use this in Microsoft 365 or Excel versions that support structured references and dynamic arrays if you want a single formula without a helper column.
Generate a country-and-count summary with UNIQUE, MAP, and REDUCE
Use this Microsoft 365 formula when you want a dynamic summary table instead of a single COUNTIF result.
Use WPS Office Spreadsheets for Simpler Country Counting Without Excel Geography Data Type Issues
If Excel Geography linked data types make COUNTIF unreliable, switch to WPS Office Spreadsheets. It opens .xlsx files, handles country names as normal text, and gives you a familiar Excel-like toolbar—free and lightweight.
- 1. Download WPS Office: Go to the WPS Office download page and install WPS Spreadsheets on your PC or Mac.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your country list.
- 3. Use COUNTIF on the country names: Select a blank cell and enter =COUNTIF(range,"United States") using the range that contains your country names in WPS.
Frequently Asked Questions
Why does COUNTIF return 0 when I use a Geography data type column?
Because a Geography cell is a linked data type, not a plain text value. Create a helper column with =A2.Name and point COUNTIF to that helper column, or use SUMPRODUCT with the .Name property.
Can I use COUNTIF directly on the Geography column?
Usually not. The cell displays a country name, but COUNTIF sees the underlying linked record. Extract the displayed property into a helper column, then apply COUNTIF to the extracted text.
What formula counts country names without a helper column?
In Microsoft 365 Excel, =SUMPRODUCT(--(country[Country].Name=B2)) can count Geography data types directly, where country[Country] is the Geography column and B2 is the criteria cell.
Will WPS Office open an .xlsx workbook that contains Geography data types?
Yes, WPS Office opens .xlsx files. However, because Geography is an Excel linked data type, the values may appear as text or lose the live link in WPS; for counting tasks, plain text is usually easier to use with COUNTIF.
What is the .Name property in a Geography data type?
It exposes the display field of the linked record, such as the country or place name. In Excel, you can reference it like A2.Name to get the text shown in a Geography cell.
