Formula Errors

How to Change Zero Results to 0.01 in Excel COUNTIF and SUMIFS

Kushani Nimanthika Oct 15, 2026 869 views

Question details

The user needs to replace the default zero (0) output of COUNTIF and SUMIFS formulas with a specific decimal value (0.01) when no matching records are found.

How to Change Zero Results to 0.01 in Excel COUNTIF and SUMIFS Formulas

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional counts and sums where a zero result is undesirable and needs to be substituted with a specific non-zero minimum value (0.01).
Observed behavior: The COUNTIF and SUMIFS functions return 0 by default when no criteria are met, which the user wants to systematically override.

Before you start

Ensure your target cells are formatted as 'Number' or 'General' with at least two decimal places so the 0.01 value is clearly visible and not rounded down to zero.

Solution 1Recommended

Use the IF Function to Replace Zero Results

Wrap your existing COUNTIF or SUMIFS formulas inside an IF statement to check if the result is 0 and replace it with 0.01.

The IF function evaluates a logical condition. By setting the condition to check if the COUNTIF or SUMIFS formula equals 0, you can specify 0.01 as the value if true, and keep the original formula's result if false.

Modify the COUNTIF Formula

Click on the cell containing your COUNTIF formula and edit it to include the IF wrapper: =IF(COUNTIF(worksheet2!B:B, worksheet1!B4)=0, 0.01, COUNTIF(worksheet2!B:B, worksheet1!B4))

Modify the SUMIFS Formula

Similarly, select your SUMIFS cell and change the formula to: =IF(SUMIFS(worksheet2!E:E, worksheet2!B:B, worksheet1!B4)=0, 0.01, SUMIFS(worksheet2!E:E, worksheet2!B:B, worksheet1!B4))

Apply and Copy the Formula

Press Enter to apply the change. If you need this logic applied to an entire column, click the cell and drag the fill handle (small square at the bottom-right corner) down the column.

Use the IF Function to Replace Zero Results

Universal Application: This IF-statement approach is highly reliable and works for almost any Excel math or lookup function where you need to avoid a strict zero output.

Solution 2

Use the MAX Function for a Shorter Formula

Use the MAX function to return whichever is greater: the formula's calculated result or 0.01.

Handle Formulas Easier with WPS

Use WPS Spreadsheet for Seamless Data Calculation

WPS Office provides a powerful, highly compatible spreadsheet application where you can execute complex functions like IF, COUNTIF, SUMIFS, and MAX exactly as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file containing the data.
2. Enter the IF statement: Click on the target cell and type your combined formula, for example: =IF(COUNTIF(worksheet2!B:B, worksheet1!B4)=0, 0.01, COUNTIF(worksheet2!B:B, worksheet1!B4)).
3. Drag to apply: Press Enter, then click the small square at the bottom-right corner of the cell and drag it down to apply the formula logic to other rows.

Fully compatible with Microsoft Excel (.xlsx) formats and formula syntaxBuilt-in formula error checking and cell auditing toolsLightweight application that opens large data files rapidlyFree to download and use for all fundamental data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I replace #N/A or #DIV/0! errors with 0.01 instead of replacing zero?

Yes, you can handle errors by using the IFERROR function instead of IF. For example, formatting your formula as =IFERROR(your_formula, 0.01) will return 0.01 anytime the primary formula encounters a calculation error.

How do I visually hide zero values completely instead of changing them to 0.01?

To hide zero values in the entire worksheet, go to File > Options > Advanced. Scroll down to the 'Display options for this worksheet' section and uncheck the box that says 'Show a zero in cells that have zero value'.

Does the MAX function alternative work if my cells contain text?

No, the MAX function only evaluates numerical data. If your dataset might cause the formula to return text strings, you should use the IF function method to safely check for exact numerical zero values.

Excel & Data Problems