Updated on 07th Dec, 23 9.1K Views

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

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:

COUNT in Excel

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:

COUNT in Excel

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:

COUNTBLANK

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

Get 100% Hike!

Master Most in Demand Skills Now !

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:

COUNTA

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:

COUNTIF

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 (*)

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 ‘?’

 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:

Using Logical Operators in COUNTIF

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:

Count the Number of Repeated Values

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

Career Transition

Intellipaat Job Guarantee Review | Intellipaat Job Assistance Review | Data Engineer Course
Got Job Promotion After Completing Artificial Intelligence Course - Intellipaat Review | Gaurav
How Can A Non Technical Person Become Data Scientist | Intellipaat Review - Melvin
Artificial Intelligence Course | Career Transition to Machine Learning Engineer - Intellipaat Review
Non Tech to Data Scientist Career Transition | Data Science Course Review - Intellipaat

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: 

COUNTIFS

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.

We tried our best to make you clear about the topic, but if you are still left with any doubts, reach out to our Community Page!

FAQs

What does the COUNT function do in Excel?

COUNT in Excel is used to tally the number of cells within a specified range that contains the numerical values. It excludes empty cells, text, errors, and non-numeric entries while counting the cells.

How does COUNTIF differ from COUNT in Excel?

COUNTIF is used to count the cells based on specific conditions or criteria, whereas COUNT is used to count all numeric values within a range. COUNTIF allows users to count cells meeting particular conditions, which offers more specific counting techniques.

Can COUNTIF use the logical operators in Excel?

Yes, we can use logical operators in COUNTIF statements in Excel. We can perform operations like greater than(>), less than (<), greater than equal to (>=), less than equal to (<=), and not equal to (!=) in Excel using the COUNTIF function.

Does COUNT in Excel count cells with formulas or text as numerical values?

No, COUNT in Excel specifically counts cells containing numerical values. It disregards the cells with text, formulas, errors, or non-numeric entries. If you need to count cells with specific content, COUNTIF or related functions are more suitable.

Course Schedule

Name Date Details
Data Scientist Course 24 Feb 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 09 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

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

Speak to our course Advisor Now !

Related Articles

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.