April 23, 2021

COUNTA function in Excel

In this lesson we will learn how the COUNTA function works in Excel. The COUNTA function is used to count the number of non-blank cells within a supplied range of cells or values.

=COUNTA(value1, [value2], …)
  • Here the arguments, value1, [value2], etc., can be values, arrays of values, or references to cell ranges within which you want to count the number of cells that are not empty.
  • The first argument is mandatory. After that there can be up to 255 additional items which are optional. 

Let’s understand in detail with a simple example as follows.

In the above table we have used the COUNTA function as follows to count the non-Blank cells in column A. It returns 3 in cell E1.

=COUNTA(A2:A6)

We have added the COUNTA function as follows to count the non-Blank cells in column B. It returns 3 in cell E2.

=COUNTA(B2:B6)

We have added the below COUNTA function to count the non-Blank cells in both of the columns A & B. It returns 6 in cell E3.

=COUNTA(A2:A6,B2:B6)

We have used the below COUNTA function to count the non-Blank cells in all 5 rows. It returns 6 in cell E4.

=COUNTA(A2:B2,A3:B3,A4:B4,A5:B5,A6:B6)

The last 2 formulas we could also write in the below way since all rows and columns are adjacent.

=COUNTA(A2:B6)

Note:

  • If cells that contain any type of information including error values, then it is considered as non-empty and counted by COUNTA function.
  • if a cell contains an empty text string or a formula that returns an empty text string, this cell is considered as non-blank and counted by COUNTA function.

You can also check here to understand how the COUNTA function works in Google Sheet.

You can also check here to understand how the COUNTBLANK function works in Google Sheet.

You can also check here to understand how the COUNTBLANK function works in Excel.

You can also check here to understand how the COUNTIF function works in Google Sheet.

You can also check here to understand how the COUNTIF function works in Excel.

You can also check here to understand how the COUNTIFS function works in Google Sheet.

You can also check here to understand how the COUNTIFS function works in Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *