How to use Excel formula for counting cells

August 1, 2022
1.3K Views
0

A free Office suite fully compatible with Microsoft Office

Any type of data in a cell may be counted with the COUNTA method, including incorrect values and blank text (). For instance, the COUNTA function counts the value returned by a formula in the range that returns an empty string. Empty cells are not counted by the COUNTA function. In this article, we will learn about how “COUNTA” formula of Excel helps in counting text.

Purpose:

Count cells with text.

Get back value

that are not blank.

Syntax

=COUNTA (value1, [value2], ...)

Arguments

value1-- might be a thing, a cell's address, or a range.

Value 2 -- is Optional a range, a cell reference, or a thing.

The COUNTAfunction counts cells that have values in them, including those that are empty or contain numbers, text, logicals, or errors (). Empty cells are not counted by COUNTA.

The count of values in the list of given arguments is returned by the COUNTA function. Multiple parameters are accepted by COUNTA in the format value1, value2, value3, etc. Arguments may consist of a single hardcoded value, a cell reference, or a range of up to 255 arguments. All values—text, numbers, percentages, mistakes, dates, times, fractions, and formulae that produce empty strings—are counted (). Cells that are empty are not taken into account.

Examples:

COUNTA is configured in the example to values between B5 and B15.

=COUNTA(B5:B15) // returns 9

Since there are 9 non-empty cells in the range B5:B15, COUNTA yields 9.

The COUNTA function totals both text and numbers:

=COUNTA(1,2,3) // returns 3

=COUNTA(1,a,b) // returns 3

=COUNTA(1,2,3,a,5%) // returns 5

non-empty cells from A1:A10 cells should be counted as follows:

=COUNTA(A1:A10) // count non-empty cells in A1:A10

A1:A10 and C1:H2 non-empty cell counts are as follows:

=COUNTA(A1:A10,C1:H2) // two ranges

null strings for counting cells with text:

It should be noted that the COUNTA includes empty strings () in the count that formulae might return. For instance, when the value in A1 is at least 10, the formula below will return OK, and when it is less than 10, it will produce an empty string ().

Both outcomes will be counted as non-empty by the COUNTA function. This example shows a workaround to disregard empty strings.

Invisible characters for counting cells with text:

Be cautious that COUNTA will also include in its count any cells that appear to be empty but really contain invisible characters or an empty string () produced by a formula. With the use of Go To > Special > Blanks, you may determine which cells are empty:

Choose a range.(Control + G) bring up the Go To dialogueClick Special, then Blanks.

Did you learn about how to use Excel formula for counting cells with text in an Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.