Formula Errors

Fix COUNTIF Returning Zero for VLOOKUP Results in Excel

Guest Writer Sep 25, 2026 869 views

Question details

The user is encountering an issue where the COUNTIF function returns a count of zero when evaluating values retrieved by a VLOOKUP formula.

Fix COUNTIF Returning Zero for VLOOKUP Results in Excel

Product: Excel
Device & OS: not provided
Scenario: Attempting to count specific occurrences of an ID or numeric value within a column populated by VLOOKUP results.
Observed behavior: The COUNTIF formula returns 0 despite the visual presence of matching criteria in the referenced range.

Before you start

Verify if the VLOOKUP results and your COUNTIF criteria are stored in different formats, such as one being a standard number and the other being a number stored as text.

Solution 1Recommended

Remove Quotation Marks for Numeric Criteria

Using quotation marks around numbers converts your criteria into text, which causes COUNTIF to fail when checking against numeric ranges.

In Excel functions, wrapping a number in quotation marks (e.g., "4020") instructs the software to treat that value strictly as a text string. If your VLOOKUP results are standard numbers, the text criteria will not match, resulting in a zero count.

Locate the formula

Click on the cell containing your COUNTIF formula that is incorrectly returning zero.

Remove quotation marks

Click into the formula bar and delete the quotation marks around your numeric criterion. For example, change =COUNTIF(Observations!I2:I113,"4020") to =COUNTIF(Observations!I2:I113,4020).

Use a cell reference

Alternatively, delete the typed number and click on a cell containing the ID to reference it dynamically, such as =COUNTIF(Observations!I2:I113,A2).

Apply the change

Press Enter to execute the updated formula and verify that the count is now accurate.

Remove Quotation Marks for Numeric Criteria

Pro Tip: Using cell references instead of hardcoded numbers makes your spreadsheet dynamic, preventing data type confusion and reducing future manual updates.

Solution 2

Standardize Data Types Between Ranges

If removing quotes doesn't work, your VLOOKUP source data might be stored as text while your criteria is numeric.

Powerful Spreadsheet Processor

Fix Formula Errors Easily with WPS Spreadsheet

WPS Spreadsheet provides intuitive error checking and seamless formula calculation. It simplifies troubleshooting complex combinations of COUNTIF and VLOOKUP, helping you easily identify and resolve data type mismatches so you get accurate counts instantly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your problematic VLOOKUP and COUNTIF formulas.
2. Inspect the formula syntax: Click on the cell with the zero result. The formula bar will highlight referenced ranges in distinct colors, making it easy to spot unnecessary quotation marks.
3. Utilize Error Checking: Click the floating yellow warning sign next to any cell containing numbers stored as text and select 'Convert to Number'.
4. Calculate instantly: Adjust your COUNTIF formula to reference a cell (e.g., =COUNTIF(Range, A2)) and press Enter to see the updated, accurate count.

100% compatible with Microsoft Excel formulas, functions, and file formatsBuilt-in error checking to instantly spot and fix numbers stored as textLightweight architecture that processes complex nested formulas instantlyClean, user-friendly interface for effortless data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIF formula work on manually typed data but not on VLOOKUP results?

VLOOKUP retrieves data exactly as it is formatted in the source dataset. If your source dataset contains numbers stored as text, VLOOKUP returns text. Manually typed data typically defaults to the correct numeric format, causing the discrepancy.

Can I force COUNTIF to treat text as numbers within the formula itself?

You cannot directly change the data type of a range within a basic COUNTIF function. However, you can force your VLOOKUP to return a number by multiplying it by 1 (e.g., =VLOOKUP(...)*1). This converts text numbers back to actual numbers before COUNTIF evaluates them.

What if my VLOOKUP results contain invisible extra spaces?

Extra spaces, including trailing or leading spaces, will cause COUNTIF to view the values as completely different text strings, returning zero. You can wrap your VLOOKUP in a TRIM function, such as =TRIM(VLOOKUP(...)), to strip away hidden spaces.

Excel & Data Problems