Count Unique Job Numbers with a Specific Ticket Type in WPS Spreadsheets
Question details
The user needs a formula that counts distinct job numbers where the ticket type is C, ignoring duplicate job number rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains job numbers in one column and ticket type values in another; the user wants a criteria-based distinct count.
- Observed behavior
- Duplicate job numbers for ticket type C are counted more than once with basic COUNTIF formulas, so a unique-count formula is required.
Confirm that the job number column and ticket type column have consistent values and no unintentional leading or trailing spaces. In newer WPS Spreadsheets versions, dynamic array formulas can spill automatically; in older versions, array formulas may require Ctrl+Shift+Enter.
Use COUNTA with UNIQUE and FILTER for a dynamic distinct count
Best for current WPS Spreadsheets versions that support dynamic array functions; it automatically filters ticket type C and returns only unique job numbers.
This method is the most straightforward when FILTER and UNIQUE are available. It filters the job number range to rows where the ticket type equals C, removes duplicates with UNIQUE, and counts the remaining items with COUNTA.
Click the cell where you want the distinct count to appear, for example C1.
Type =COUNTA(UNIQUE(FILTER(A2:A11,B2:B11="C"))) and press Enter. The formula filters A2:A11 where B2:B11 equals C, extracts unique job numbers, and counts them.
Verify that WPS Spreadsheets displays the expected unique job number count for ticket type C.
Count distinct values in older WPS Spreadsheets with SUMPRODUCT and COUNTIFS
Use this array formula when FILTER and UNIQUE are not available or when you need a backward-compatible calculation.
Count Unique Job Numbers by Ticket Type Directly in WPS Spreadsheets
WPS Spreadsheets can handle this task directly with either the modern COUNTA/UNIQUE/FILTER formula or the classic SUMPRODUCT/COUNTIFS array formula, so you can get an accurate distinct count without switching tools.
- 1. Open your workbook: Launch WPS Office and open the .xlsx or .xls workbook that contains the job number and ticket type columns.
- 2. Select the result cell: Click an empty cell where you can safely display the distinct count.
- 3. Apply the recommended formula: Enter =COUNTA(UNIQUE(FILTER(A2:A11,B2:B11="C"))) in newer versions, or use the SUMPRODUCT/COUNTIFS formula with Ctrl+Shift+Enter in older versions.
- 4. Review the result: Confirm the returned number matches the unique job numbers with ticket type C.
Frequently Asked Questions
Does WPS Spreadsheets support UNIQUE and FILTER?
Recent WPS Office versions support dynamic array functions including UNIQUE and FILTER. If your version does not, use the SUMPRODUCT and COUNTIFS array formula instead.
Why does my SUMPRODUCT formula return a #DIV/0! error?
Wrap the division with IFERROR as shown: =SUMPRODUCT(IFERROR(1/COUNTIFS(A2:A11,A2:A11,B2:B11,"C"),0)). In older versions, confirm with Ctrl+Shift+Enter.
Can I use this formula with an Excel Table instead of a fixed range?
Yes. Replace A2:A11 and B2:B11 with your table column references, such as Table1[Job Number] and Table1[Ticket Type]. The formula then updates automatically as rows are added.
What if my ticket type values have extra spaces?
The condition B2:B11="C" requires exact matches. Use TRIM on the source data or change the condition to compare against a cleaned helper column if leading or trailing spaces are present.
How do I count unique job numbers for multiple ticket types?
Use a condition that includes multiple ticket types, such as =COUNTA(UNIQUE(FILTER(A2:A11,(B2:B11="C")+(B2:B11="D")))) in dynamic-array versions, or adjust the COUNTIFS criteria accordingly.
