Which function returns the number of cells in a cell range that contain data?

The COUNTA function is an inbuild statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2.

The COUNTA function can count cells containing several types of data values. This includes text, numbers, Boolean values, date/time values, error values, and empty text strings (“”). It returns a numeric value.

Table of contents

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: COUNTA Excel Function (wallstreetmojo.com)

The Syntax of the COUNTA Function

The syntax of the function is given as follows:

The function accepts the following arguments:

• Value1: This represents the values that are to be counted.
• Value2: This also represents the values that are to be counted.

The first argument is mandatory, while the second is optional. The argument can be a range, a cell, a value, an array of values, or a reference to cell rangesReference To Cell RangesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

Note: A maximum of 255 arguments can be entered in MS Excel 2007 and the subsequent versions. The earlier versions of Excel can handle 30 arguments only.

How to Count Non-Blank Cells using COUNTA?

The COUNTA function is used when there is a need to count the number of cells in single or multiple non-blank ranges. The cell range can also be non-adjacent.

For example, for counting cells in the range B1:B50, the formula is “=COUNTA (B1:B50).”

The function also counts the number of value arguments provided. The value argument is a parameter that is neither a cell nor a range of cells.

The COUNTA function is used to count the following categories of data:

• The number of customers in a list
• The number of transactions in a given time period
• The number of tests submitted by students
• The number of employees having an e-mail address
• The number of presentations delivered

You can download this COUNTA Function Excel Template here – 

Example #1 – Single Range

The data is given in the succeeding image. We want to determine the number of non-blank cells in the range A2:A7.

1. We use the following formula

   “=COUNTA(A2:A7)”
   
   

   

2. The COUNTA function counts the number of data cells from A2 to A7. It returns 5 because cell A5 is blank. Hence, all the values are counted except the blank value of cell A5.
   
   

Example #2 – Multiple Ranges

The data is given in the succeeding image. We want to determine the number of non-blank cells in two cell ranges A2:A7 and B2:B4. 

We use the following formula.

“=COUNTA(A2:A7,B2:B4)”

The COUNTA function counts the number of data cells from A2 to A7 and B2 to B4. It returns 7 because the cells A5 and B3 are blank. Hence, all values are counted except the blank cell values.

Example #3 – Multiple Columns Range

The grades in Maths, English, and Computer of 5 students are shown in the succeeding image. For each subject, we want to count the number of students who have been allotted a grade.

We use the following formulae.

“=COUNTA(B2:B6)”
“=COUNTA(C2:C6)”
“=COUNTA(D2:D6)”

The COUNTA function counts the number of grades in Maths from B2 to B6, in English from C2 to C6, and in Computer from D2 to D6. It returns the values 3, 2, and 3 respectively.

Example #4 – Value Arguments With Range

Let us supply direct values and a range to the COUNTA function.

Working on the previous example, let us assume that the missing students “Neha” and “Rahul” also appeared for the Maths test.

We use the following formula.

“=COUNTA(B2:B6,“Neha”,”Rahul”)”

The COUNTA function counts the number of non-empty cells in the range B2:B6. To this output, it adds “2” due to the two additional value arguments–“Neha” and “Rahul.” Hence, the total number of students who appeared for the Maths test is 5.

Example #5 – Multiple Value Arguments

Let us supply direct values to the COUNTA function.

We want to find the number of non-blank values within a set of direct values.

We use the following formula.

“=COUNTA(1,2,“”,text,TRUE)”

The COUNTA function returns the number of non-blank values from the total direct values. Hence, it returns 5.

Example #6 – Contiguous Rectangle Range

We want to find the number of non-blank cells in the contiguous rectangle A2 to B6, shown in the succeeding image.

In the formula, we specify the entire range beginning from the upper-left cell to the lower-right cell.

“=COUNTA(A2:B6)”

The COUNTA function counts the number of cells containing data in the range A2:B6. It returns 7 because cells A5, B3, and B5 are blank. Hence, all values are counted except those in the blank cells.

The Characteristics of COUNTA Function

The features of the function are listed as follows:

• It counts all non-blank cells, unlike the COUNT function, which counts only numeric values.
• It only counts the existing values without summing them.
• It returns an error if incorrect arguments are provided to it.
• It counts cells that contain invisible characters. For instance, an empty string (“”) returned by a formula is also counted.
• It counts the hard-coded values. For example, “=COUNTA(“c”,2,4,””)” returns 4.
• It counts every non-empty cell, cell range, and value argument as 1.
• It counts the cell in which space is entered, implying that only the absolutely empty cells are excluded from the count.

Note: To count the cells that meet certain conditions, the COUNTIFCOUNTIFThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more or COUNTIFSCOUNTIFSThe COUNTIFS excel function counts the values of the supplied range based on one or multiple criteria (conditions). The supplied range can be single or multiple and adjacent or non-adjacent. Being a statistical function of Excel, the COUNTIFS supports the usage of comparison operators and wildcard characters. read more functions are used.

Frequently Asked Questions

Define the COUNTA function in Excel.

The COUNTA function counts the non-empty cells in a range or list of items. In case multiple ranges are supplied to the function, it is not necessary that every range is of the same size.

The COUNTA function belongs to the family of COUNT functions. The syntax of the function is given as follows:
“COUNTA(value1,[value2],…)”

The “value 1” (mandatory) and “value 2” arguments represent the values to be counted.

What is the purpose of using the COUNTA function in Excel?

The objective of the COUNTA function is to provide the exact number of cells containing data. The function is used when a quick count of the listed items is required. For example, the accountant uses the COUNTA function while reviewing the inventory numbers.

In simple words, COUNTA stands for “count all” with the only exception being the blank cells.

What is the difference between the COUNTA and the COUNT functions of Excel?

The differences between the two functions are listed as follows:

• The COUNTA function counts the non-empty cells within a specified range. On the other hand, the COUNT function counts the number of cells containing numeric values.
• The COUNTA function is more inclusive than the COUNT function because the former counts all kinds of data values. In contrast, the latter ignores the non-numeric values when provided as a cell reference.

Note: While deciding which of the two functions to use, the data to be counted must be analyzed.

Key Takeaways

• The COUNTA function counts the number of non-blank cells in a range.
• The COUNTA function counts the cells containing text, numbers, Boolean values, date/time values, error values, and empty text strings (“”).
• The syntax of the COUNTA function is–“COUNTA(value1,[value2],…),” where “value 1” (mandatory) and “value 2” are the values to be counted.
• The argument of the COUNTA function can be a range, a cell, a value, an array of values, or a reference to cell ranges.
• The COUNTA function returns a numeric value and excludes only the absolutely empty cells from the count.
• In Excel 2007 and the subsequent versions, a maximum of 255 arguments to the COUNTA function can be entered.

Recommended Articles

This has been a guide to COUNTA Function in Excel. Here we discuss how to count non-blank cells using the COUNTA formula along with examples. You may learn more about Excel from the following articles –

• Excel Descriptive StatisticsExcel Descriptive StatisticsDescriptive statistics is used to summarize information available in statistics, and there is a descriptive statistics function in Excel as well. This built-in tool is found in the data tab, in the data analysis section.read more
• FORECAST in ExcelFORECAST In ExcelThe FORECAST function in Excel is used to calculate or predict the future value based on existing values and the statistical value of the forecast. If we know the past data, we may use the function to forecast the future value.read more
• Count Unique Excel ValuesCount Unique Excel ValuesIn Excel, there are two ways to count values: 1) using the Sum and Countif function, and 2) using the SUMPRODUCT and Countif function.

  Which function count the number of cells that contain data in a data range?

  The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

  Which function provides the number of cells within a range that contain a value whether numbers or text?

  You use the COUNT function in Excel to count the number of cells that contain numerical values.

  Which function is used to count the number of cells that contain numbers in a range Countif () SUM () Sumif () count ()?

  Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition. For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.

  Which function returns the count of the number of rows in the range?

  The most basic formula used is =ROWS(rng). The function counted the number of rows and returned a numerical value as the result.