OmniDocExcel COUNT function: Count the number of numeric cells
Explain clearly what the Excel COUNT function counts and what it ignores: numbers and dates are included, but text, blanks, and error values are not. Attached are the troubleshooting methods for textual numbers and how to count them after exporting data from PDF tables.
Key points of this article
- COUNT only counts numbers and dates in the range, text, blanks, and error values will be skipped.
- The numbers look normal but are not included. Most of them are text numbers. They are converted into numerical values first and then counted.
- Use COUNTA to count non-empty cells, COUNTBLANK to count blank cells, and COUNTIF to count based on conditions.
- Before exporting from a PDF table to Excel, the scanned document must first recognize the text and check the numbers item by item.
When making tables, we often ask: How many numbers are there in this column? If you just want to count how many amounts, fractions, or numbers there are, Excel's COUNT function will suffice. But it only recognizes numbers, and will quietly skip when it encounters text, blank spaces, or error values. The result does not match the number seen by the naked eye. This is where many people are confused.
This article is written for those who frequently organize tables, especially those who extract data from PDF reports or scanned documents. After reading, you will know what COUNT counts and what it ignores, how to troubleshoot when numbers are not included, and the division of labor between it and COUNTA and COUNTIF. Here we only talk about COUNT itself, and there is a special article on counting by conditions.
How does the COUNT function count cells? The conclusion in one sentence is to just count numbers
The COUNT function counts the number of values in a specified range. It is written as =COUNT(值 1, [值 2], ...). The parameter can be a range of cells, a single cell or the number itself. The key to judging whether a cell will be counted is whether it is a real value.
| your situation | Recommended practices | reason |
|---|---|---|
| Just want to count how many values there are in a column | =COUNT(A2:A100) | COUNT only counts numbers, text and blanks will be automatically skipped |
| Count non-empty cells, including text | Use COUNTA instead | COUNT does not include text, the result will be less |
| The numbers look normal but are not included | Convert to numerical value first and then count | Textual numbers are not recognized by COUNT |
| Want to count blank cells | Use COUNTBLANK | COUNT does not count blanks |
| Need to count after filtering by conditions | Use COUNTIF or COUNTIFS | COUNT has no condition parameters |
What does COUNT count?
- Numbers: Whole numbers, decimals, negative numbers and scientific notation all count.
- Date and Time: Excel internally stores dates as numbers, so dates entered normally will be included.
- Content written directly into the parameter list: such as TRUE, FALSE entered directly, or numeric text enclosed in quotes, such as
"5".
What COUNT ignores
- Blank cells.
- Ordinary text, such as "Not filled in" and "To be determined".
- Error values such as #DIV/0!, #N/A. They will be skipped and will not cause the formula to report an error.
- TRUE and FALSE in the zone.
- The number in the area saved as text.
Where it’s easy to get confused
A common problem is textual numbers. When copying data from web pages, PDFs or other systems, the numbers may be in text format, and a small green triangle often appears in the upper left corner of the cell. They appear to count, but COUNT does not. There are two troubleshooting methods: one is to select the data and check whether the cell format is "text"; the other is to enter =ISNUMBER(A2) in the blank cell and return FALSE indicating that A2 is not a number.
Another source of confusion is COUNT and COUNTA. COUNTA counts all non-empty cells, including text, and COUNT only counts numbers. If there are both amounts and remarks in the same column, the results of the two will naturally be different. This is a normal phenomenon and not an error in the formula.
COUNT can also count multiple areas at one time, such as =COUNT(A2:A50, C2:C50), and the result is the sum of the numbers in the two areas.
📊 Core comparison
| function | Statistical objects | Whether it is counted in the text | Typical uses |
|---|---|---|---|
| COUNT | Numbers and dates | No | There are several amounts and scores |
| COUNTA | All non-empty cells | Yes | The number of records that have been filled in |
| COUNTBLANK | Blank cells | Not applicable | Check any missing areas |
| COUNTIF | Cells that meet the conditions | It depends on the conditions | Counting the number of people in a certain department |
How to actually use it
If the data comes from PDF tables, you can first organize the table into Excel and then use COUNT for statistics. Common steps are as follows:
- Open the PDF in the OmniDoc desktop client, which can be downloaded from download.omnidoc.top.
- If it is a scan, first use OCR to recognize the text. Recognition results must be checked item by item, especially the number 0 and the letters O, 1, and l can easily be confused; if one digit is wrong, the statistical results will be incorrect as well.
- Export the table as Excel format, open it, and check the number columns. Cells with green triangles indicate they may be text-type numbers. Select them and convert them to values as prompted.
- Enter
=COUNT(B2:B200)in a blank cell and replace the area with your actual range. - For the same area, use
=COUNTA(B2:B200)to calculate; the difference between the two is the number of non-numeric content such as text and errors, making verification easier.
❓ Frequently Asked Questions (FAQ)
1. COUNT: The result is less than mine. Where did it go wrong?
Most of the time, these are text-type numbers, or numbers with spaces before and after. First, check the cell format, then check each cell one by one using ISNUMBER.
2. Will dates be counted by COUNT?
Yes. Excel stores dates as serial numbers, which are essentially numeric values. However, if the date is only saved in text form, such as when entering a text cell format, it will not be counted.
3. Can COUNT count multiple regions at the same time?
Yes. =COUNT(A2:A50, C2:C50) will count the number of numbers in each of the two regions and then add the results.
4. If there is an error value in the range, will COUNT report an error?
No, error values will be skipped directly. But COUNT cannot tell you how many error values there are; if you want to count the errors themselves, you need to use other methods.
5. Can the number after OCR on the scanned document be directly used as COUNT?
You can count, but you have to check first. OCR may recognize numbers as similar letters; such content is considered text and will not be counted, requiring manual correction before counting.
📝 Summary
The COUNT function only counts numbers and dates; text, spaces, and error values are skipped. If statistical results do not match expectations, first check for text-based numbers; When counting non-empty cells or counting by condition, use COUNTA or COUNTIF instead.
