COUNT in Excel: Formula and Implementation

In this blog, we will understand the COUNT function in Excel and how it is used to make data analysis easier. We will also look into the implementation of various types of COUNT functions and their use in Excel.

Table of Contents

• What is COUNT in Excel?
• Types of COUNT Functions in Excel
  • COUNTBLANK
  • COUNTA
  • COUNTIF
  • COUNTIFs
• Conclusion
• FAQs

Watch this Excel course video to learn more about its concepts:

What is COUNT in Excel?

COUNT in Excel is a built-in function used to count the number of cells within a range that contains numerical values. It skips the cells that contain empty cells, text, or errors. This function is useful in determining how many numeric values are present in the dataset and makes the calculation easier in Excel.

The syntax for COUNT in Excel is as follows:

Syntax: 

COUNT(value1,[value2],…)  

The parameters of the COUNT function include comma-separated values. This function returns the total count of the numeric values specified.

Example:

In the above example, we pass parameters to the COUNT function, and it counts the total number of values in it.

Count Range: We can also specify the range of the cells we want to count in Excel. It helps us quantify the data.

The syntax for count range in Excel is as follows:

Syntax:

COUNT(value1:value2) 

Example:

The above example counts the number of cells in which data is present from the range A1:A8.

Enroll in this professional Data Analytics Course to learn more about data analytics from experts.

Types of COUNT Functions in Excel

Excel has various types of COUNT functions based on different conditions per the user’s requirements.

Let us understand the different types of COUNT functions in Excel along with their implementation:

COUNTBLANK

COUNTBLANK is a simple COUNT function used to count the number of blank or empty cells within a specific range. It helps to identify total counts of cells that do not contain any data, including cells that appear to be empty but may contain spaces or formula-generated empty strings. This function is particularly useful for data analysis, especially when assigning the gaps in a dataset.

Syntax: 

=COUNTBLANK(range)

The COUNTBLANK function works on a specified range as a parameter.

Example:

In the above example, we can count the number of blanks in the range A2:E11, using COUNTBLANK function.

COUNTA

COUNTA in Excel is used to count the number of cells within a range that are non-empty. Unlike the COUNT function, which counts only the numeric values, COUNTA counts all non-blank cells in a specified range.

Syntax:

=COUNTA(value1,[value2],…)

Example:

In the above example, we have counted the total number of cells, including blank cells, in the range C2:D11.

Prepare yourself for the industry by going through these Excel Interview Questions!

COUNTIF

COUNTIF in Excel is a function used to count cells within a specified range of cells that meet specific criteria. It allows the user to count the number of cells that satisfy a certain condition.

Syntax:

=COUNTIF(range, criteria)

• Range refers to the cells in which you want to perform the COUNT function.
• Criteria define which cells to count within the range for a particular condition.

Example:

The above example shows the count of people whose gender is female in the range (C2:D11).

Under the COUNTIF function, we also have various other uses, such as wildcard characters, logical operators, and counting the number of repeated values:

We will understand this in detail in the coming sections.

Using Wildcard Characters in COUNTIF 

Wildcard characters like asterisk (*) or question mark (?) can be used in the COUNTIF function in Excel to count the number of cells that match a certain pattern or contain specific characters.

Example 1: Use of an Asterisk (*)

The above example counts the Emp_Name whose name ends with S in the range B2:B11.

Example 2: Use of Question Mark ‘?’

This example displays the count of employees where there are 4 characters in their name. 

Using Logical Operators in COUNTIF

We can use logical operators like greater than (>), less than (<), equal to (=), greater than equals to (>=), etc., in the COUNTIF function within a certain range of cells based on certain conditions on specified criteria.

Example:

The formula used in the above example counts the cells within the range of C2 to C11 that have values greater than 25.

Count the Number of Repeated Values

We can also count the number of repeated values in a specific range under certain conditions using the COUNTIF function.

Example:

In the above example, we have counted the number of employees whose age is 23 in the range C2:C11.

COUNTIFS

COUNTIFS in Excel is a function that counts cells that meet multiple criteria within different ranges. It allows users to count the number of cells that satisfy several conditions simultaneously.

Syntax:

=COUNTIFS(range1, criteria1,[range2, criteria2]…)

• Range1 will be the range of cells to which the first condition (criteria1) will be applied.
• Criteria1 defines the condition for the function that will work on Range1.
• The criteria can be any number, string, or expression, or it can also be a cell reference, for example, “>=25”, ”intellipaat”, or A1.
• Multiple range-criteria sets can be added, and they must be comma-separated.

Example: 

In the above example, we are using the COUNTIFS function in Excel where the age of the employee is greater than 25 and the gender is male (M).

Conclusion

In conclusion, understanding the COUNT function in Excel is important for efficient data analysis. From COUNTBLANK to COUNTIF  and its variations like COUNTIFS, utilizing wildcard characters and logical operators helps in data precision. These functions are not just about tallying the data; they are used to identify duplicates and apply various conditions to a particular range of data, which helps in easily solving complex calculations. We use these functions to enhance accurate and insightful data interpretation in Excel.

FAQs