COUNTA function in Excel, function to count cells containing data with specific usage and examples

The COUNTA function in Excel is a useful data statistics function. Below are details on how to use the function to count cells with data in Excel .

Microsoft Excel has long been famous as a smart, professional and useful spreadsheet application in managing and analyzing data. It offers a rich, useful set of features including intelligent functions.

You probably already know that Excel can perform calculations with numbers. But did you know that it can also count other types of information? One of the simplest examples of that is the COUNTA function .

COUNTA looks at a range of cells and tells you how many cells contain data. In other words, it finds cells that are not empty. This can be useful in many different situations.

If you have never used Excel functions, you can refer to the lessons on TipsMake. Essentially, COUNTA works the same on all versions of Excel, similar to other spreadsheet applications.

Video tutorial on using the COUNTA function in Excel

How to count the number of cells containing data in Excel

The COUNTA function in Excel has the function formula COUNTA(value1, [value2], .) .

In which value 1, value 2 are the list of values, cell ranges or references you want to count. Value 1 is a required parameter and the remaining values ​​are optional, containing a maximum of 255 values.

Attention:

  1. The function counts cells containing any data type except blank cells.
  2. If you do not want to count logical and text values ​​or error values, use the COUNT counting function.
  3. If you want to count values ​​that satisfy certain conditions, use the COUNTIF or COUNTIFS function.

For example, the COUNTA function counts Excel cells in a range

For example, with a data table with different values, use the COUNTA function to count the number of cells containing data in the table.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 1

In the result of the Value box, enter the formula =COUNTA(C2:C7) and press Enter.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 2

The result showing 6 means there are 6 cells containing data in the Value column, regardless of the values ​​in the cells including text and dates.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 3

If you delete data in any cell, the number of resulting cells automatically decreases. Then the COUNTA function will automatically ignore those blank cells.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 4

For example, the COUNTA function counts Excel cells in multiple ranges

With exercises to count cells in multiple Excel ranges, COUNTA still counts exactly the same as when counting cells in a range. We have an Excel file with 2 data tables as below. Counts the number of cells containing data in the Value column.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 5

In the input box to calculate the number of non-empty cells, enter the formula =COUNTA(C2:C5,C8:C12) and press Enter. In which C2:C5 is the first data area that wants to count the number of cells and C8:C12 is the second data area that wants to count the number of cells.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 6

The result displaying 6 means that the total number of cells containing data on both data tables in the Value column range is 6. Even if you count the number of cells across multiple ranges, the COUNTA function will automatically ignore empty cells and only count cells that contain data regardless of data type.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 7

So using the COUNTA function to count the number of cells containing data in Excel is very simple and quick. You can use the function to count non-empty cells in a range or many different ranges. The numbers are counted without distinction except for empty cells.

Wishing you success!

4 ★ | 1 Vote

May be interested

  • Instructions on how to count words in cells in ExcelInstructions on how to count words in cells in Excel
    in many cases you want to count the number of characters in a text string (text string) to calculate or combine with other functions, or to do something. you can then use the trim function, combining the len function, the substitute function. using the trim function to remove spaces, use the substitute function to replace the current text with new text in a text string, ...
  • Basic Excel functions that anyone must knowBasic Excel functions that anyone must know
    the basic functions in excel such as the excel function, the excel statistics function we summarized below will be very helpful for you who often have to work on excel spreadsheets, especially in the field of accounting. let's refer to offline.
  • MATCH function in Excel, usage and examplesMATCH function in Excel, usage and examples
    the match function in excel helps determine the position of a value in a list or range of cells. this is a useful tool when working with large data sets and can be combined with the index function to retrieve more precise information.
  • COUNTA function - The function performs counting non-blank cells in the list of arguments in ExcelCOUNTA function - The function performs counting non-blank cells in the list of arguments in Excel
    counta function: the function performs counting non-blank cells in the list of arguments. syntax: counta (value1, [value2], ...)
  • VLOOKUP function to use and specific examplesVLOOKUP function to use and specific examples
    vlookup is one of the most useful excel functions but few understand it. in this article, we will help you understand vlookup with practical examples, such as grading students and staff using the vlookup function.
  • How to calculate and color blank cells in ExcelHow to calculate and color blank cells in Excel
    counting and coloring blank cells in excel will rely on excel functions and conditions to perform.
  • COUNTIFS function in ExcelCOUNTIFS function in Excel
    the count () function counts cells that contain numbers and the countif () function counts cells according to a specified condition. but sometimes you need to count cells under many conditions, it is not possible to apply these two functions. in this case, you need to use the countifs () function to perform it.
  • Excel date function - Usage and examplesExcel date function - Usage and examples
    the date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
  • OR function in Excel, how to use the OR function, and examplesOR function in Excel, how to use the OR function, and examples
    the following article details the meaning and usage of the or - logic function in excel. description: the or function is a function that returns the logical value in excel.
  • Function Address - The function returns the address of a cell in Excel (usage, examples, examples)Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
    the following article dexterity software will introduce readers to the address function one of the most popular reference and lookup functions in excel.