COUNT Function in Excel – Shiksha Online

COUNT Function in Excel – Shiksha Online

5 mins read1.6K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Sep 15, 2023 11:54 IST

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

2022_03_Count-function-1.jpg

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

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.

Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more

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
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.
Freeze Panes in Excel – Shiksha Online
Freeze Panes in Excel – Shiksha Online
Discover the powerful feature of freeze panes in Excel that allows you to lock rows or columns, ensuring important information stays visible as you explore large datasets or create complex...read more

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.
MEDIAN Function in MS Excel
MEDIAN Function in MS Excel
The article covers the MEDIAN function in MS Excel. It is in continuation to the MS Excel basic functions series.
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more

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

FAQs

How do I use the COUNT function?

To use the COUNT function, enter "=COUNT(range)" in a cell, replacing "range" with the actual range of cells you want to count.

Can the COUNT function count both numbers and text?

No, the COUNT function only counts cells that contain numbers. To count cells with both numbers and text, you can use the COUNTA function.

Can I count cells based on a specific condition using the COUNT function?

No, the COUNT function only counts cells that contain numbers. To count cells based on a specific condition, you can use the COUNTIF or COUNTIFS functions.

Does the COUNT function count empty cells?

No, the COUNT function does not count empty cells. It only counts cells that contain numbers.

Can the COUNT function handle errors in cells?

Yes, the COUNT function ignores cell errors and only counts cells containing valid numbers.

Can I use the COUNT function to count cells in a filtered range?

Yes, the COUNT function considers cells in a filtered range and counts only the visible cells.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments