How to use the Excel COUNTA function: count the number of non-empty cells

Key points of this article

COUNTA counts the number of non-empty cells in the statistical area. Text, numbers, dates and error values will all be included. It is suitable for counting the number of filled-in records.
COUNT only counts numbers. When the area is filled with text, the result will be smaller. The two cannot replace each other.
Title rows, seemingly blank spaces, and empty text returned by formulas will all affect the COUNTA results and need to be checked first.
After statistics, use ROWS to compare the total number of rows. The difference is the number of blank rows, which can quickly detect missing fills or overcounts.

If you have a customer registration form, questionnaire collection record or inventory list, you often encounter a simple question: How many items have been filled in? Many people subconsciously count rows, but there are often blank rows, blank spaces, and only half-filled records in tables, making manual counting errors easy. Excel's COUNTA function is prepared for this kind of scenario. It counts the number of "not empty" cells in a range.

This article is written for readers who already know how to use basic formulas, but still can't tell the difference between COUNTA and COUNT. The names of the two functions are very similar, but the results may be very different: COUNT only counts numbers, and COUNTA counts all non-empty contents. After reading this article, you will be able to determine which function to use, write the COUNTA formula, and check whether the results are reliable.

How to choose between COUNTA and COUNT? One sentence conclusion

If you want to count the cells with content in the area (whether it is text, numbers or dates), use COUNTA; if you want to count pure numbers (such as amounts, fractions), use COUNT. As long as there is text mixed in the area, the results will be different.

your situation	Recommended practices	reason
Count the filled-in text columns such as name, mobile phone number, etc.	COUNTA	COUNT does not count characters, the result will be small or even 0
Pure numeric columns such as statistical amounts and quantities	Either COUNT or COUNTA will work	When the column has only numbers, the two results are the same
Numbers and text are mixed together in a column, I just want to count the numbers	COUNT	COUNTA will include text
Want to know how many blank cells there are	COUNTBLANK	COUNTA only counts non-empty numbers. For blank spaces, use another function.
The "" returned by the formula appears to be empty	Check first before deciding whether to use COUNTA or COUNTBLANK	COUNTA will count "" as non-empty

Before you start: Confirm these points first

Make sure to judge the following items before taking action, otherwise even if the formula is written correctly, the result may not be as expected.

Whether the statistical area contains a header row. COUNTA will also count titles. For example, if A1 says "name", when counting A1:A30, the result will be 1 more than the actual number of people.
Whether the cell is really empty. Spaces and line breaks that are invisible to the naked eye also belong to the content and will be counted in COUNTA. This is most commonly seen when data is copied and pasted from web pages or chat history.
Whether any formula returns empty text. For example, the result of a formula such as =IF(condition,"",content) looks blank, but in COUNTA it looks like a non-empty cell.
Whether rows outside the filter need to be excluded. COUNTA does not distinguish between filtering status, and rows that are hidden after filtering will still be counted. When counting only visible rows, use the SUBTOTAL function instead.
Whether the data source is standardized. If the data comes from a table in PDF, it can be converted to Excel format before statistics. The PDF editor of the OmniDoc desktop client supports exporting PDF to Excel, and then use the above method to count.

Steps to use COUNTA to count non-empty cells in Excel

Assuming that the name is written in column A and the data is from row 2 to row 31, the following steps can be followed directly.

Open the table and determine the columns and data range to be counted, such as A2:A31.
Enter =COUNTA(A2:A31) in a blank cell and press Enter to confirm.
The result is the number of non-empty cells in the range. If you want to count the total of multiple columns, you can write =COUNTA(A2:C31), and the non-empty cells in the three columns will be included together.
If you want to count two discontinuous areas, separate the two parameters with commas, such as =COUNTA(A2:A31,C2:C31), the result is the total of the two areas.
Use =ROWS(A2:A31) to get the total number of rows in the area, and then compare it with the result of COUNTA. The difference between the two is the number of blank rows.
If you need to count the entire column, you can write =COUNTA(A:A), but this will also count the header row, and the result will be subtracted by 1.
If you have doubts about the result of a cell, you can enter =LEN(A2) next to it to check the number of characters. A result of 0 means it is truly empty, and a result greater than 0 means there are spaces or other characters inside.

Check each item after completion

After getting the results, it is recommended to check them one by one according to the table, especially tables with large amounts of data and mixed sources.

Check items	How to see	What should you do if there's a problem?
Is the result 1 more than manual counting?	Check whether the first row of the statistical area is a title	Change the starting point of the region to the first row of the data
The results were more than expected, and there were cells that appeared to be blank	Use LEN to check the character count of these cells	After clearing the excess spaces, recount the data
The results were more than expected, and the cells contained formulas	Check if the formula returns blank text equal to ""	Use functions like COUNTIF to exclude empty text
No changes in the results after screening	Confirm whether you only need to count visible rows	Switched to =SUBTOTAL(103, region)
The numeric column is 0 with COUNT, but COUNTA has a number	Check if the numbers are saved as text	Convert to numerical format before further statistics

📊 Core Comparison

The COUNTA, COUNT, and COUNTBLANK functions are often used together, with the following differences. Suppose A1 to A5 are: Zhang 3, 25, Blank, Li 4, 30.

Function	What are the statistics?	Results from the above areas	Typical uses
COUNTA	All non-empty cells	4	Count the number of records that have been completed
COUNT	All digital cells	2	Count the number of numerical items
COUNTBLANK	All blank cells	1	Find the missing spots

❓ Frequently Asked Questions (FAQ)

1. Can COUNTA count the number of text characters?

No. COUNTA counts the number of cells, not the number of characters. To count the number of characters, you need to use functions like LEN to calculate the character length of each cell.

2. Will error values be counted by COUNTA?

Yes. For example, error values like #N/A also appear to COUNTA as non-null content. If the table contains many error values, the statistical results will be too large. It is recommended to handle the error values first before compiling.

3. Can COUNTA count only the non-empty cells that meet the criteria?

COUNTA itself does not have conditions; to count by condition, you need to use functions like COUNTIFS and adjust the conditional writing based on your data columns.

4. Why are cells that look blank also counted?

Common causes include spaces or newlines in the cell, or the formula returning empty text. After checking with the LEN function, you can determine which situation it is.

📝 Summary

COUNTA counts non-empty cells within an area; any content is counted, while COUNT counts only numbers. The division of labor between the two must be clearly defined. The key to mastering how to use the Excel function counta is to first check the header line, spaces, and empty text, then use ROWS to compare the total number of rows. Follow the steps in this article to ensure your statistical results stand up to verification.

Open OmniDoc and process your table file