Formula Errors

How to Use Excel IF and COUNTIF to Show a Blank Instead of Zero

Partner Editor Oct 10, 2026 870 views

Question details

The user needs an Excel formula that returns an empty or blank cell instead of the number zero when a COUNTIF function finds no matches in a specified range.

How to Use Excel IF and COUNTIF to Show a Blank Instead of Zero

Product: Excel
Device & OS: not provided
Scenario: Counting specific names or text criteria across a horizontal or vertical data range without cluttering the spreadsheet with zeros.
Observed behavior: By default, the COUNTIF function returns a '0' when the specified criteria is not found, which visually clutters the worksheet when an empty cell is preferred.

Before you start

Identify the exact data range you want to search and decide what specific criteria (like a name or number) you are counting before writing your formula.

Solution 1Recommended

Combine IF and COUNTIF Functions

Use an IF statement to check if the COUNTIF result equals zero. If true, the formula outputs an empty text string; if false, it returns the actual count. This works in all spreadsheet versions.

This traditional method wraps your counting logic inside a logical test. While the COUNTIF function calculates twice (once for the test, once for the result), it remains highly reliable and backwards-compatible with older spreadsheet software.

Select the destination cell

Click on the specific cell in your spreadsheet where you want the calculated count to appear.

Enter the nested formula

Type the formula `=IF(COUNTIF(L3:DJ3, "Stephen")=0, "", COUNTIF(L3:DJ3, "Stephen"))` into the formula bar. Replace `L3:DJ3` with your actual data range and `"Stephen"` with the criteria you are looking for.

Apply the formula

Press the Enter key on your keyboard. The cell will now remain completely blank if there are zero matches, or it will display the accurate count if matches exist.

Tip for cell references: Instead of hardcoding a name like "Stephen" into the formula, you can reference another cell containing the search term, for example: `=IF(COUNTIF(L3:DJ3, A1)=0, "", COUNTIF(L3:DJ3, A1))`.

Solution 2

Use the LET Function (For Microsoft 365 & Office 2021)

If you use a modern spreadsheet software version, the LET function can shorten your formula by defining the COUNTIF result as a variable.

Advanced Spreadsheet Formulas

Easily Handle Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and mathematical functions like IF, COUNTIF, and LET. You can seamlessly execute nested formulas to hide zero values and manage large datasets efficiently.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to count.
2. Input your combined formula: Select a blank cell and type your IF and COUNTIF formula, such as `=IF(COUNTIF(A1:A20,"Target")=0, "", COUNTIF(A1:A20,"Target"))`.
3. Press Enter to execute: Hit Enter. WPS Spreadsheet instantly calculates the result, hiding any zero values to keep your workspace clean.

100% compatible with Microsoft Excel (.xlsx) formats and functionsFree and lightweight office suite with lightning-fast processingSupports over 400 advanced formulas for complex data analysisFamiliar user interface makes it easy to switch without a learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I show a dash or text instead of a blank cell when the count is zero?

Yes. In the formula `=IF(COUNTIF(range, criteria)=0, "", COUNTIF(range, criteria))`, you simply replace the empty double quotes `""` with `"-"` or your desired text string, like `"Not Found"`.

Does this IF and COUNTIF method work for vertical columns as well?

Absolutely. The COUNTIF function operates exactly the same whether your range is horizontal (like `L3:DJ3`) or vertical (like `A1:A100`). Just highlight the column range you need.

Why is my COUNTIF formula returning an error message?

This usually happens due to a syntax error. Ensure that you have matched all parentheses perfectly, used commas to separate the arguments, and placed quotation marks around any hardcoded text criteria.

Can I apply this same logic to the SUMIF function?

Yes, you can wrap a SUMIF function inside an IF statement exactly like COUNTIF to hide zero sums. For example: `=IF(SUMIF(range, criteria, sum_range)=0, "", SUMIF(range, criteria, sum_range))`.

Excel & Data Problems