How to Count Unique Names in an Excel Column on Mac with WPS Office
Question details
The user needs a reliable formula to count distinct nonblank names in a column on a Mac, including Excel versions that do not support modern dynamic-array functions.
- Product
- WPS Office Spreadsheets
- Device & OS
- macOS
- Scenario
- Counting the number of unique, nonblank names in a column in a worksheet on a Mac.
- Observed behavior
- The worksheet contains blank cells, and the user needs each distinct nonblank name counted once with a formula that works in older Excel for Mac versions.
Confirm the exact range containing the names (C4:C641 in this example) and make sure blank cells do not contain spaces, tabs, or line breaks, because invisible characters can make otherwise identical names count separately.
Use SUMPRODUCT and COUNTIF to Count Unique Nonblank Names
Use this formula when you need compatibility with older Excel for Mac versions and WPS Office Spreadsheets without dynamic-array functions.
This classic formula divides each matched value by the number of times it appears, so every distinct nonblank name contributes exactly 1 to the total.
Click an empty cell, such as E4, where you want the unique name count to appear.
Type =SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641)) into the formula bar.
Press Enter or Return on your Mac. The cell displays the number of distinct nonblank names in C4:C641.
Use UNIQUE and FILTER in Modern Excel Versions
Use this shorter dynamic-array formula if your version of Excel for Mac or WPS Office supports UNIQUE and FILTER.
Count Unique Names with WPS Office Spreadsheets on Mac
Open the same workbook in WPS Office Spreadsheets, use the SUMPRODUCT/COUNTIF formula, and get the unique nonblank name count without upgrading Microsoft Excel.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office on your Mac and open the .xlsx or .xls file that contains the name column.
- 2. Select the result cell: Click an empty cell, for example E4, to hold the unique count.
- 3. Enter the compatible formula: Type =SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641)) into the formula bar.
- 4. Press Enter to return the count: Press Return on your Mac. WPS Spreadsheets calculates the number of distinct nonblank names in the selected range.
- 5. Save the workbook: Save the file as .xlsx to preserve compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the SUMPRODUCT formula ignore blank cells?
Because the condition C4:C641<>"" creates TRUE/FALSE values; blank cells are excluded before COUNTIF divides the counts, so only nonblank names contribute.
Does this formula work in Excel for Mac 2019?
Yes. It uses SUMPRODUCT and COUNTIF, which do not require dynamic-array functions, so it works in Excel for Mac 2019 and earlier and in WPS Office.
Can I use a different range instead of C4:C641?
Yes. Replace C4:C641 with your actual range, such as A2:A500. Avoid selecting entire columns in very old Excel versions if performance becomes slow.
What if two names look the same but are counted separately?
Check for leading or trailing spaces, tabs, or line breaks. Clean the source data or create a helper column with =TRIM(C4) before counting.
Will this work in WPS Office Spreadsheets on Mac?
Yes. Open the file in WPS Office Spreadsheets, use the same formula, and press Return. The result matches Excel for Mac.
