How to Count Unique Numbers and Text Values Automatically in WPS Office Excel
Question details
The user wants to automatically create a second Excel table that lists unique tree descriptions, such as different sizes and tree types, and counts each distinct value without manually entering duplicates.
- Product
- WPS Office Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- Working with a source table containing tree descriptions that include sizes and types, the user needs a summary table of unique values and their counts.
- Observed behavior
- The goal is to have a second table update automatically with unique values and counts, with no manual duplicate removal.
Verify that your WPS Office version supports dynamic array formulas such as UNIQUE. Open the workbook and make sure the source data is in an Excel Table or a contiguous range with a clear Raw Description header.
Use UNIQUE and COUNTIF to list and count distinct values automatically
Best for a dynamic summary that updates when the source data changes; use UNIQUE to extract distinct values and COUNTIF to count each occurrence.
The UNIQUE function returns a spill range of distinct values from a source column. Pair it with COUNTIF to count how many times each returned value appears in the original range. This method works best when your version of WPS Office Excel supports dynamic array formulas.
Click the cell where you want the unique list to begin, such as F2 on the same worksheet as the source table.
Type =UNIQUE(Table1[Raw Description]) and press Enter. If the data is not in an Excel Table, use =UNIQUE(A2:A100) with the actual source range.
In F1 and G1, enter headings such as Unique Description and Count so the summary table is clear.
In G2, type =COUNTIF(Table1[Raw Description],F2) and drag the fill handle down to the last unique value.
Add or change a value in the source table and confirm that the unique list and counts recalculate automatically.
Create a PivotTable to group and count unique values
Use this when you prefer an interactive report with filters and want to avoid maintaining formulas.
Count unique values automatically with WPS Office Excel
Use the built-in UNIQUE and COUNTIF functions in WPS Office Excel to create a live summary table that lists distinct numbers and text values and updates as your source data changes.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office, open the .xlsx file containing the source table, and click the worksheet with the Raw Description column.
- 2. Enter the UNIQUE formula: Click the cell where the summary should start, type =UNIQUE(Table1[Raw Description]), and press Enter.
- 3. Add COUNTIF for counts: In the adjacent cell, type =COUNTIF(Table1[Raw Description],F2) and drag the fill handle down to count each unique value.
- 4. Format the summary table: Select the unique list and count column, apply bold headers, and choose a table style from the Home tab.
- 5. Save and verify: Save the workbook as .xlsx or .csv to keep the formulas, and verify that the summary recalculates when source data changes.
Frequently Asked Questions
Can UNIQUE be used with both numbers and text in the same column?
Yes. UNIQUE returns distinct values from a range containing numbers, text, or mixed values. For example, =UNIQUE(A2:A100) lists each different number or text entry once.
What if my version of WPS Office does not support UNIQUE?
Use a PivotTable instead. Click any cell in the source data, go to Insert > PivotTable, add the description field to Rows and Values, and set the value field to Count.
How do COUNTIF and UNIQUE work together to count unique values?
UNIQUE returns the distinct list, then COUNTIF counts each returned value in the original range. For example, use =COUNTIF(Table1[Raw Description],F2) next to each unique cell.
Can I count unique values by category, such as tree type?
Yes. Add the tree type field as a filter or column in a PivotTable, or use COUNTIFS with a type criterion alongside COUNTIF.
Will the unique list update automatically when I add rows?
Yes. If you reference a full table column such as Table1[Raw Description], the UNIQUE and COUNTIF formulas update automatically as rows are added to the table.
