Excel COUNTA Function

last modified October 5, 2026

The COUNTA function counts all non-empty cells in a range. It works with any data type including numbers, text, errors, and formulas. This tutorial provides a comprehensive guide to using COUNTA with detailed examples. You'll learn basic syntax, practical applications, and advanced techniques to master this essential Excel function.

COUNTA Function Basics

The COUNTA function counts cells that are not empty. It handles all data types unlike COUNT which only counts numbers. The syntax is simple and flexible.

Component	Description
Function Name	COUNTA
Syntax	=COUNTA(value1, [value2], ...)
Arguments	1-255 items to count
Return Value	Count of non-empty cells

This table breaks down the essential components of the COUNTA function. It shows the function name, basic syntax format, argument limits, and return value characteristics.

Argument	Meaning
value1	Required. The first value, cell reference, or range to count.
value2, ...	Optional. Additional values, cell references, or ranges, up to a total of 255 arguments.

COUNTA counts any cell that is not truly empty. That includes numbers, text, logical values such as TRUE and FALSE, error values such as #N/A, and formulas that return an empty string (""). Only a completely blank cell is ignored.

Basic COUNTA Example

This example demonstrates the simplest use of COUNTA with a range of cells containing different data types.

Basic COUNTA formula

=COUNTA(A1:A5)

This formula counts all non-empty cells in range A1:A5. It will count numbers, text, logical values, errors, and formulas that return any value.

COUNTA with Mixed Data Types

COUNTA counts all non-empty cells regardless of data type. This example shows how it handles mixed content.

A	B
10
Text
TRUE
#N/A
	=COUNTA(A1:A4)

The table shows a range with different data types. COUNTA counts all cells except empty ones, demonstrating its versatility compared to COUNT.

COUNTA with mixed data

=COUNTA(A1:A4)

This formula counts all non-empty cells in A1:A4. The result is 4 (number, text, logical value, and error). COUNTA is the most inclusive counting function in Excel.

COUNTA vs COUNT vs COUNTBLANK

The three counting functions answer different questions. COUNT counts numeric cells, COUNTA counts all non-empty cells, and COUNTBLANK counts empty cells. This example compares them on the same range.

A	B	C	D
10	=COUNT(A1:A5)	=COUNTA(A1:A5)	=COUNTBLANK(A1:A5)
Text
TRUE

=""

The range A1:A5 holds a number, text, a logical value, a truly empty cell, and a formula that returns an empty string. COUNT returns 1 (the number only), COUNTA returns 4 (every non-empty cell, including the "" formula), and COUNTBLANK returns 2 (the empty cell and the "" formula).

COUNT, COUNTA, and COUNTBLANK compared

=COUNT(A1:A5)      ' 1, numeric cells only
=COUNTA(A1:A5)     ' 4, all non-empty cells
=COUNTBLANK(A1:A5) ' 2, empty cell plus "" formula

Notice that COUNTA and COUNTBLANK overlap: the cell containing "" is counted by both, so the two results add up to 6 even though the range has only 5 cells. This overlap is a common source of confusion. Use COUNT for numbers, COUNTA for anything that is not blank, and COUNTBLANK when you need to find gaps.

COUNTA with Blank Cells

COUNTA ignores truly empty cells but counts cells with formulas that return empty strings. This example shows this important distinction.

A	B

=""
Data
	=COUNTA(A1:A3)

The table contains a truly empty cell (A1), a formula returning "" (A2), and text (A3). COUNTA counts A2 and A3 but not A1, showing how it handles different types of "empty".

COUNTA with blank and formula cells

=COUNTA(A1:A3)

This formula returns 2 - it counts A2 (formula returning "") and A3 ("Data"), but not A1 (truly empty). A cell that contains only a space, " ", is also treated as non-empty and is counted, because it holds a character. If such cells should be ignored, subtract them with =COUNTA(A1:A3)-COUNTIF(A1:A3," "). COUNTBLANK reports the truly empty cell and the "" formula, but it does not report a cell that holds a space.

COUNTA with Logical Values

COUNTA counts logical values in a range, while COUNT ignores them. This difference matters when a column mixes numbers with TRUE and FALSE, for example a checklist.

A	B	C
10	=COUNT(A1:A4)	=COUNTA(A1:A4)
TRUE
FALSE

The range A1:A4 contains one number, two logical values, and one empty cell. COUNT returns 1, because logical values inside a range are ignored. COUNTA returns 3, because it treats both TRUE and FALSE as non-empty.

Logical values are counted

=COUNT(A1:A4)  ' 1, logicals in a range are ignored
=COUNTA(A1:A4) ' 3, TRUE and FALSE count as non-empty

Remember that COUNTA only reports whether a cell is occupied, not what type of value it holds. To count only the cells that are TRUE, use =COUNTIF(A1:A4,TRUE) instead.

COUNTA with Multiple Ranges

COUNTA can count non-empty cells across multiple ranges or individual cells. This example demonstrates this flexibility.

A	B	C
Item1
	Item2
		=COUNTA(A1:A2,B1:B2)

The table shows how to count non-empty cells across two separate ranges (A1:A2 and B1:B2). COUNTA combines the counts from both ranges in its total.

COUNTA with multiple ranges

=COUNTA(A1:A2, B1:B2)

This formula counts non-empty cells in both A1:A2 (1) and B1:B2 (1), returning 2. You can specify up to 255 ranges or individual cells as arguments.

COUNTA with Dynamic Arrays

In modern Excel, COUNTA works with dynamic array formulas. This example counts filtered results from another function.

COUNTA with FILTER function

=COUNTA(FILTER(A1:A10, B1:B10="Active"))

This formula counts all "Active" items in A1:A10. FILTER creates a dynamic array of matching items, and COUNTA counts them. This is powerful for conditional counting without COUNTIF.

COUNTA with Entire Rows/Columns

COUNTA can count non-empty cells in entire rows or columns. This example shows counting a whole column.

COUNTA entire column

=COUNTA(A:A)

This formula counts all non-empty cells in column A. Be cautious with full column references in large worksheets as they can slow down performance.

COUNTA with Error Values

Unlike some functions, COUNTA includes cells containing error values in its count. This example demonstrates this behavior.

A	B
10
#N/A
#VALUE!
	=COUNTA(A1:A3)

The table contains numbers, errors, and blanks. COUNTA counts both numbers and error values, showing it treats errors as non-empty cells.

COUNTA with error values

=COUNTA(A1:A3)

This formula returns 3, counting A1 (10), A2 (#N/A), and A3 (#VALUE!). If you need to exclude errors, combine COUNTA with IFERROR in an array formula.

COUNTA with Named Ranges

COUNTA works well with named ranges, improving formula readability. This example shows COUNTA with a named range.

A	B
North
South
East
West
	=COUNTA(Regions)

The table assumes cells A1:A4 are named "Regions". The COUNTA formula references this named range instead of cell addresses, making it more readable.

COUNTA with named range

=COUNTA(Regions)

This formula counts all non-empty cells in the named range "Regions". The result is 4. Named ranges make formulas self-documenting and easier to maintain.

COUNTA Errors and Troubleshooting

Cause	Symptom	Fix
A formula returns "" (empty text)	A blank-looking cell is counted	Expected behavior; clear the formula or locate it with COUNTBLANK
A cell contains only a space	Count is higher than expected	Remove the space, or subtract it with COUNTIF(range," ")
Range contains error values	COUNTA still returns a number	Expected; use COUNT to ignore errors or COUNTIF for specific values
Numbers stored as text	COUNT misses them but COUNTA includes them	Convert the text to numbers with VALUE before counting
FILTER returns no matching rows	#CALC! error from FILTER	Wrap the expression: =IFERROR(COUNTA(FILTER(...)),0)
Whole-column reference on a large sheet	Slow recalculation	Limit the range to the used rows
More than 255 arguments	"Too many arguments" error	COUNTA accepts at most 255 arguments

The COUNTA function is essential for counting non-empty cells regardless of content type. Its defining rule is simple: any cell that holds something is counted, whether that something is a number, text, a logical value, an error, or a formula that returns an empty string. Only a truly empty cell is skipped, and a cell holding a single space is not empty at all. Keep that rule in mind alongside its companions - COUNT for numbers only and COUNTBLANK for gaps - and you can describe any dataset accurately. Mastering these distinctions will improve your data analysis and worksheet auditing.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks