WPS Spreadsheet: Count Cells with Values Excluding Empty IF Results
Question details
The user needs to count cells that display values while excluding formulas that return an empty string.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains formulas such as IF formulas that return "", and the user wants an accurate count of visibly populated cells.
- Observed behavior
- COUNTA includes cells containing formulas that return an empty string, producing a higher count than the number of visible values.
Identify the exact range to count, such as A2:A100, and check whether the cells contain formulas returning "" or actual blank cells. Also check for values containing only spaces, because spaces are not empty strings.
Use COUNTIF to exclude empty string results
Use this method when the range contains formulas that return "" and you want to count cells with non-empty displayed results.
COUNTIF with the "<>" criterion counts cells that are not empty and excludes formula results that display as an empty string.
Click the cell where you want the count to appear.
Type =COUNTIF(A2:A100,"<>") and replace A2:A100 with the range you need to evaluate.
Press Enter. WPS Spreadsheet returns the count of cells with non-empty results and excludes formulas that return "".
Count numeric results only with COUNT
Use this method when only numbers should be counted and text values, empty strings, and blank cells should be excluded.
Count visible text and numbers with LEN
Use this method when cells may contain spaces or other displayed text and you need to test whether the result length is greater than zero.
Count Non-Empty Formula Results in WPS Spreadsheet
WPS Spreadsheet provides COUNTIF, COUNT, and SUMPRODUCT functions to count displayed values accurately while handling formulas that return empty strings. Its familiar spreadsheet interface makes it easy to edit the range and verify the result.
- 1. Open the worksheet: Launch WPS Office, open your spreadsheet, and locate the range containing the formulas.
- 2. Choose a result cell: Click an empty cell where the count should appear.
- 3. Enter the appropriate formula: Use =COUNTIF(A2:A100,"<>") for all non-empty results, =COUNT(A2:A100) for numbers only, or =SUMPRODUCT(--(LEN(A2:A100)>0)) when testing visible characters.
- 4. Confirm and check the count: Press Enter and compare the result with the visibly populated cells in the selected range.
Frequently Asked Questions
Why does COUNTA count cells that appear empty?
COUNTA counts a cell when it contains a formula, even if that formula returns an empty string such as "". Use COUNTIF(range,"<>") to exclude those displayed-empty results.
What formula counts only numeric values in WPS Spreadsheet?
Use =COUNT(A2:A100), replacing the range with your data range. COUNT ignores text, blank cells, and formulas that return empty strings.
How can I count both text and numbers but exclude empty IF results?
Use =COUNTIF(A2:A100,"<>") for standard non-empty results. If you need to test the displayed length explicitly, use =SUMPRODUCT(--(LEN(A2:A100)>0)).
Why does my count still include cells that look blank?
The cells may contain spaces or other invisible characters. COUNTIF can treat such cells as non-empty, so clean the source data or use a formula that removes spaces before testing the length.
