COUNT Function in Excel – Shiksha Online

Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.

If you regularly work with Excel, you know that counting many cells manually can be error-prone. If we want to know how many cells contain numbers, we can use Excel’s COUNT function. You will know how many cells contain numbers within your set range in seconds. This article will discuss counting values in MS Excel using COUNT, COUNTA, and COUNTIF functions.

• COUNT Function
• Example 1 – COUNT function
• Example 2 – COUNT function
• COUNTA function
• Example – COUNTA function
• COUNTIF function
• Example – COUNTIF function

COUNT Function

The COUNT function in Excel allows us to automate long processes of counting values. It is one of the most used and easy functions in the category of statistical functions.

Use – The COUNT function counts those cells that contain numbers within a specific range.

Syntax – COUNT (value1, [value2], …)

This function has only 2 arguments:

Value 1: Mandatory. It indicates the first element, the cell reference or range where you want to count the numbers.

Value 2:  Optional. You can add 255 items, reference cells, or additional ranges.

The COUNT function is handy when you need a quick count of the data points in a range without any complicated conditions or criteria.

Example 1 – COUNT function

Below is the data of some Indian startups. We will count the number of companies.

Press Enter

The COUNT functions help to determine how many cells have numeric data.

Example 2 – COUNT

Now, we have another data set with the sales number of certain household products. You can see some of the cells don’t have data. It makes it difficult to understand sales numbers between 2018 – 2022. 

We can use the COUNT function to identify the instances of sales in these years. Let’s select the cells

You can also count using AutoSum. If you select all the cells with numeric values, the AutoSum function counts cells column-wise.

Observations:

• The arguments can contain or reference various data types, but the COUNT function only counts numbers. 
• The COUNT function counts arguments in numbers, dates, or a text representation of the numbers.
• You can use the COUNTA function to count logical values, text, or errors

COUNTA function

Often, we have to count the cells having text or other values. We will use the COUNTA in Excel to count all cells that contain something in a range or array. This formula has identical syntax to the COUNT. Making general counts of different values, together or separately, is useful.

Category – Statistical Functions

Use – The COUNTA function counts all non-empty cells within a specified range,

Syntax – COUNTA (value1, [value2], …)

This function has 2 arguments:

Value1: Value1 is mandatory. Indicate the first element, the cell reference or range where you want to count the numbers.

Value2: Optional. It allows you to put up to 255 items, reference cells, or additional ranges in which you want to count numbers.

Example – COUNTA function 

In the below data, we will count the number of people working in an organization using the COUNTA.

We will start writing =COUNTA. The function will count the number of cells with data in it.

Press Enter

The data suggests that we have 254 employees in an organization from different countries.

Observations to take into account:

• The COUNTA function counts cells that contain any type of information, including error values ​​and empty text (“”).
• If the range has a formula that returns an empty string, the COUNTA function counts that value.

COUNTIF function in Excel

COUNTIF allows us to count cells based on custom criteria. This formula has identical syntax to the COUNT. It is very useful to make general counts of different values, together or separately.

Category – Statistical Functions

Use – COUNTIF counts the number of cells meeting a criterion. For example, counting the times a given city appears in a customer list.

Syntax – COUNTIF (range, criteria)

This function has 2 arguments:

Range: Mandatory. Indicates the range of cells to which the condition will be applied to consider your account.

Condition: Mandatory. It refers to the condition that the cells of the range must meet to be included in the account.

Example – COUNTIF function

Here, we want to count the number of people from any particular country. We will select an empty cell, start typing =COUNTIF, mention the cell range, add a comma and then quote China unquote.

Press Enter

The COUNTIF function counts 46 people from China.

Observations to take into account:

• The condition argument is not case-sensitive. If we put “CROATIA” or “Croatia”, the result will be the same.
• Wildcard characters, such as the question mark (?) and asterisk (*), can be used in criteria. 
• The question mark replaces any single character. The asterisk replaces any sequence of characters. If you want to search for a question mark or an asterisk, type a tilde (~) before the character.

I hope this article helped you to understand and work with COUNT, COUNTA, and COUNTIF functions in MS Excel. Do practice these functions on a data set to have a better understanding.

Must Explore – Excel Tutorials

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst