Excel COUNT Returns 0 for Imported Quantities: Find Text Numbers Before a Stock Check

If =COUNT(B2:B7) returns 0 or a lower result than the number of visible quantities, one or more imported values are text rather than numbers. Convert the quantity cells to real numbers in a helper column, then count the converted results again.

An inventory export can look complete while a stock check says that only some rows contain quantities. Every cell may display digits, but COUNT only counts cells Excel stores as numbers. Imports, copy-and-paste operations, and downloaded reports can leave quantities stored as text.

The safest approach is to leave the imported column unchanged at first. Create a helper column, convert each value with VALUE, and compare the count before and after conversion. That gives you a clear audit check before you use the quantities in a stock review.

CONTENTS

Quick Fix

For a small one-off list, Excel may show a warning indicator beside text numbers. Select the affected cells, select the error indicator, and choose Convert to Number. However, for an imported inventory list, a helper column is usually safer because you can inspect every conversion before replacing source values.

Use COUNT when you need to count genuine numeric quantities.
Use a helper column when you need to prove that imported text was converted successfully.
Do not use a higher count as a reason to fill missing quantities with zero.

For other ways to spot and correct the issue, see this guide to fixing numbers stored as text in Excel.

Why COUNT Ignores Visible Digits

COUNT counts numbers in a referenced range; it does not count text values in that range just because the text looks like a number. For example, the characters 60 can be either a numeric value or a text value. The display may look the same, while formula results differ.

Microsoft’s COUNT function documentation explains that COUNT counts cells containing numbers in a range. This is why a completed-looking imported column can produce a result that is lower than the visible row count.

Default alignment can sometimes provide a clue: text often appears left-aligned and numbers often appear right-aligned. Treat that only as a clue, not a test. Formatting can change alignment, so a before-and-after count is more dependable.

Worked Stock-Count Example

Enter 125 in B2 and 40 in B6 as numbers. Enter ’60, ’18, ‘200, and ’75 in B3:B5 and B7; the leading apostrophe makes those entries text for this reproducible example.

The apostrophe is an entry marker and normally is not displayed in the cell. As a result, the six quantities can appear alike even though only two are numeric values before conversion.

Cell	Quantity displayed	Stored as
`B2`	125	Number
`B3`	60	Text
`B4`	18	Text
`B5`	200	Text
`B6`	40	Number
`B7`	75	Text

In an empty cell such as D2, enter:

=COUNT(B2:B7)

Before conversion, =COUNT(B2:B7) returns 2. After the helper formulas convert every quantity, =COUNT(C2:C7) returns 6.

The initial result of 2 is correct for the stored data: only 125 and 40 are numbers. It is not a count of cells that merely look populated. This distinction is useful before a stock check because it separates a data-type problem from a missing-data problem.

Convert Text Quantities in a Helper Column

Use column C as an audit column rather than immediately changing column B. Type Checked Quantity in C1. In C2, enter:

=VALUE(B2)

In C2, enter =VALUE(B2) and fill the formula down through C7. Then use =COUNT(C2:C7) to verify the converted quantity column.

Each helper formula refers to the matching imported cell. For example, =VALUE(B3) interprets the text 60 in B3 as the number 60. After filling down, scan column C before taking any further action. The converted entries should show 125, 60, 18, 200, 40, and 75 with no errors.

Check	Formula	Expected result
Original import	`=COUNT(B2:B7)`	2
Converted audit column	`=COUNT(C2:C7)`	6

If the helper column is correct and a downstream workbook process requires the original column to contain numbers, copy C2:C7. Select B2, then use Paste Special > Values. Save a copy of the workbook or retain the original import first, because pasting values replaces the text entries and removes the audit formulas from the destination.

Microsoft’s guide to converting numbers stored as text to numbers describes both the warning-menu option and the VALUE approach.

Small Verification Formula

You can also verify the basic behavior of COUNT with a separate, clean numeric range. Enter 125 in A1, 60 in A2, and 18 in A3, then enter this formula in an empty cell:

=COUNT(A1:A3)

The expected result is 3. All three cells contain numeric values, so COUNT includes every cell in the range. This simple check helps distinguish a COUNT issue from an import-data issue: if this formula returns 3, COUNT is working normally and the original quantity column needs inspection.

Boundary Check: Zero Is a Quantity

A real numeric zero is still a number: =COUNT(A1:A1) returns 1 when A1 contains 0. Do not treat a zero stock quantity as a missing entry.

This is an important stock-report boundary case. Zero can mean that an item is out of stock. A blank cell can mean that the source system did not supply a quantity. They may require different follow-up actions, even though neither situation should be fixed by guessing a value.

Troubleshooting When the Count Is Still Wrong

Symptom	Likely cause	Practical response
`#VALUE!` appears in column C	The imported entry is not a recognizable number.	Inspect the source value. Correct its format or source data rather than hiding the error.
The converted count remains too low	Some cells are blank, or the formula excludes rows.	Compare the COUNT range with the complete quantity list and investigate genuinely missing values.
Values look numeric but do not convert	Leading or trailing spaces may be present.	Remove the spaces first, then repeat the helper-column check.
The count does not change after conversion	The formula is referring to the wrong range.	Confirm that the COUNT range covers the same rows as the quantity list.

VALUE can convert text that Excel recognizes as a number, but it returns #VALUE! for entries such as N/A, currency symbols or separators that do not match the workbook’s regional settings, and numbers mixed with other text.

If a quantity cell is genuinely blank, COUNT does not count it after conversion either. Investigate the missing import value instead of replacing blanks with zero merely to increase the count. If formulas are unexpectedly using empty cells, review how to troubleshoot an Excel formula that refers to a blank cell before changing the imported data.

A number-looking value with a leading or trailing space may not convert as expected. Remove the spaces first, then repeat the helper-column check. If the formula refers to the wrong range, converting the data will not change the result. Check that the COUNT range covers the same rows as the quantity list.

Compatibility and Fallback

COUNT and VALUE are available in Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. The helper-column method is the reliable fallback when a Convert to Number alert is not available.

The warning indicator is convenient when it appears, but it is not necessary for this workflow. A helper column provides a visible conversion result, exposes values that cannot be interpreted as numbers, and gives you a count you can compare before altering a stock import.

Sources

Practice with the exact example data

Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Compare your result with the expected result below. These are synthetic practice data.

Example 1 — Main case

Cell	Input value
A1	125
A2	60
A3	18

Formula in AA1: =COUNT(A1:A3)

Expected result: 3

Example 2 — Boundary case

Cell	Input value
A1	0

Formula in AA1: =COUNT(A1:A1)

Expected result: 1

The numeric examples were checked with a limited calculator for SUM, AVERAGE, MIN, MAX and COUNT. They were not executed in Microsoft Excel. This check does not verify every claim in the article.

Download the practice sheet (HTML) — open in your browser or print. Available after this article is published.