How to Use COUNT, COUNTA, COUNTBLANK, COUNTIF Functions in Excel

In this lesson; we are going to learn, how to use COUNT, COUNTA, COUNTBLANK and COUNTIF Functions in Microsoft Excel:

In Microsoft Excel, we have four different Count functions which we use to count the number of cells in certain ranges or range. These functions are totally different from Excel Sum function because Count functions are not able to add data. Count functions use to count only numeric data, and ignore text data or information.

Function # 1: Count:

By using this function, you can count the number of numeric entries from data by ignoring text, blanks and errors.

Formula of COUNT Function:

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

To test the accurate use of Count function, we would like to share with you an example data sheet as mentioned below:

In the above mentioned data, we have text, error, blanks, dates with numeric entries. By using Count function you can easily count numeric entries by eliminating all text, errors, blanks & date formats etc by following below mentioned steps:

Step 1: First Add a "Count" column on data sheet.

Step 2: Now next step is to Apply Count Formula on Cell "E3" by horizontally selection of Rough data as mentioned below:

Step 3: Now drag down this formula to all cells as mentioned below:

Now you can see that Count Function is working properly because it is counting only numeric data as mentioned in below snapshot:

Function # 2: COUNTA:

By using this function, you can count the number of numeric entries or text entries from data by ignoring blank cells.

Formula of COUNTA function:

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

To test this function, we again quote this example as mentioned below: 

To apply COUNTA functions, you will follow all Steps as mentioned on COUNT functions just by replacing COUNT formula to COUNTA formula as mentioned below:

In below mentioned snapshot, you can see that COUNTA function is not able to count blank cell:

Function # 3: COUNTBLANK:

By using this function, you can easily count the quantity of blank cells in bulk data range. 

Formula of COUNTBLANK function:

=COUNTBLANK(range)

To test this function, we would like to share with you an example as mentioned below:

Example: Suppose that you are the owner of ESS company, and you want to conduct the balloting activity to all department employees regarding implementation of "stop smoking" policy. 

Below mentioned snapshot is the result of balloting activity with Yes or No department votes:

By using COUNTBLANK function, you can easily count blank cells to identify that how many votes still not yet registered for "Stop smoking" policy implementation. as mentioned below:

Step 1: First Add "Vote not yet Registered" column as mentioned below:

Step 2: Now next step is to Insert the COUNTBLANK formula in Cell "C14" as mentioned below:

After applying this formula to all cell, you can see the result as mentioned below:

Function # 4: COUNTIF:

By using this function, you can easily count the number of items in selected range according to user defined criteria.

Formula of COUNTIF function:

=COUNTIF(range, criteria)

We would like to share with you an example as mentioned below:

Example: Suppose you are working as a HR manager and you wanted to find out some values or result with the help of certain criteria or condition.

Data mentioned below:

Now Apply COUNTIF functions to find out results;

Result # 1: To find out No of Manager, please apply below mentioned formula:

Result # 2: To find out Salaries Over $ 20,000, please see below snapshot:

Result # 3: To find out No of Employees in Marketing Department, please see below screenshot:

In below mentioned screenshot, you can see that COUNTIF function is working properly:

We hope that you like this lesson.

---Thank You---