Count Duplicate Values in WPS Spreadsheets Without Counting Blank Formula Cells
Question details
Count repeated values without including rows where a related formula cell appears blank because it returns an empty string.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- You need to identify or count duplicate wave numbers or other repeated values while considering only rows with a visible related value.
- Observed behavior
- COUNTIF or COUNTIFS may include formula-containing cells that look blank, causing duplicate counts to be higher than expected.
Identify the column that contains the values to count and the related column that determines whether a row has a visible result. Confirm that both criteria ranges begin and end on the same rows.
Use COUNTIFS with a nonblank related-cell criterion
Use this method when duplicate values should be counted only for rows whose related cell has a visible value.
Add a second COUNTIFS criterion for the related results column. The "<>" criterion excludes rows where that related cell evaluates to an empty string.
In WPS Spreadsheets, click the cell where you want the duplicate count to appear, such as E2.
Type =IF(AND(COUNTIF($C$2:$C$100,C2)>1,C2<>""),COUNTIFS($C$2:$C$100,C2,$D$2:$D$100,"<>"),"") and press Enter. Column C is the value to count, and column D is the related cell that must not appear blank.
Replace $C$2:$C$100 and $D$2:$D$100 with your actual data ranges. Keep the two ranges the same height, and replace C2 with the value cell in the current row.
Select the formula cell, then drag the fill handle down or use Home > Fill > Down to apply the formula to the remaining rows.
Count one specific value while excluding blank-looking formula results
Use this shorter formula when you only need the total for one known number or text value.
Count qualifying duplicate values with WPS Spreadsheets
WPS Spreadsheets lets you use COUNTIF, COUNTIFS, IF, and relative references to count duplicate entries while excluding rows with blank-looking formula results. Enter the formula once and fill it down your worksheet to review qualifying duplicates quickly.
- 1. Open the worksheet in WPS Spreadsheets: Open your workbook and select the sheet containing the values and related formula results.
- 2. Select the output column: Click the first cell beside your data where the duplicate count should be shown.
- 3. Enter the COUNTIFS formula: Enter a formula such as =COUNTIFS($C$2:$C$100,C2,$D$2:$D$100,"<>") and adjust the columns and ranges for your sheet.
- 4. Add duplicate-only logic if needed: Wrap the count with IF and COUNTIF to show a result only when the current value occurs more than once.
- 5. Fill the formula to remaining rows: Drag the fill handle down to calculate counts for each row.
Frequently Asked Questions
Why does COUNTIF count cells that look blank?
A cell can contain a formula even when its displayed result is an empty string (""). Add a related range to COUNTIFS with the "<>" criterion to count only rows that meet your visible-value condition.
How do I show counts only for duplicate values in WPS Spreadsheets?
Use IF with COUNTIF, such as =IF(COUNTIF($C$2:$C$100,C2)>1,COUNTIF($C$2:$C$100,C2),""). Add COUNTIFS when another column must also contain a visible value.
Do COUNTIFS criteria ranges need to be the same size?
Yes. For example, if the first criteria range is C2:C100, the related criteria range should cover the same rows, such as D2:D100.
Can I count a specific wave number and ignore blank related results?
Yes. Use =COUNTIFS(A2:A100,"5000094270",B2:B100,"<>"). Replace the number and ranges with the wave number and columns in your worksheet.
How can I leave the result blank when no qualifying values are found?
Wrap the count in IF: =IF(COUNTIFS(A2:A100,"5000094270",B2:B100,"<>")>0,COUNTIFS(A2:A100,"5000094270",B2:B100,"<>"),"").
