How to Use Excel COUNT Function (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

In general, the COUNT function in Excel helps us to count the number of cells containing numerical values within a given range. This is one of the more popular statistical functions in Excel. However, this COUNT function is used to get the number of entries in a number field that is in a range or array of numbers. In this article, I will share a complete idea through 4 ideal examples of how to use the COUNT function in Excel, both independently and with other Excel functions.

overview of excel count function

The above screenshot is an overview of the article, representing an application COUNT of the function in the range D5:D11 in Excel. You’ll learn more about the methods to use the COUNT function properly in the following sections of this article.


Introduction to Excel COUNT Function

The COUNT function is categorized under the statistical function in Excel. It was first initiated in Excel in 2000.

Excel COUNT Function

  • Function Objective

Counts the number of cells in a range that contains numbers.

  • Syntax

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

  • Arguments
Argument Required/Optional Explanation
value1 Required Pass an item, cell reference, or range.
value2 Optional Pass an optional item, cell reference, or range.
  • Returning Parameter

Returns the number of cells that contain numbers only.

Note:

  • Usually, this function’s argument can be individual items, cell references, or ranges up to a total of 255 arguments.
  • However, the COUNT function ignores the logical values TRUE and FALSE.
  • In addition to these, this function also ignores text values and empty cells.
  • Versions

Workable from Excel version Excel 2007.


COUNT Function in Excel: 4 Ideal Examples

Usually, you can use the COUNT function on various occasions. Let’s explore some common uses of COUNT. Moreover, we will be using different datasets for different examples. However, keep in mind that these are just some basic examples to demonstrate the usage of the function in different scenarios. At the same time, the usage of the function can go a long way in developing useful formulas for automation. For the purpose of demonstration, I have used the following sample dataset.

Sample Dataset


Example 1: Use COUNT Function to Count Numbers in Given Range

Using the COUNT function, we can easily count any range of numbers. To show the process, let’s assume we have a dataset of some Foods with their name, Date, and Sales. Now, we will count the number of sales by counting the sales cells.

📌 Steps:

  • Initially, select cell D13 and write down the following formula.

=COUNT(D5:D11)

Use COUNT Function to Count Numbers in Given Range

  • Finally, hit the Enter key to get the result.

calculating number of cells in a given range


Example 2: Insert COUNT in Excel to Get the Number of Valid Dates

Furthermore, another feature of the COUNT function is that it will only count valid dates. The process is quite simple and easy. For the purpose of demonstration, I have changed the dataset slightly. However, go through the following steps.

📌 Steps:

  • Firstly, write the following formula on cell D13.

=COUNT(C5:C11)

Insert COUNT in Excel to Get Number of Valid Dates

  • Lastly, hit Enter button to get the Number of Valid Dates.

 

finding number of valid dates using function

Read More: How to Count Date Occurrences in Excel


Example 3: Count Ignoring Invalid Cells Using COUNT

At the time of counting, the COUNT function ignores invalid cells. However, let’s assume that in our dataset, in the Sales column, some of the rows contain text or strings. Hence, we want to calculate the number of sales by ignoring those invalid sales. Therefore, follow the steps below.

📌 Steps:

  • Initially, insert the following formula on cell D13.

=COUNT(C5:C11)

Count Ignoring Invalid Cells Using COUNT

  • Lastly, hit Enter from the keyboard to get the final output.

calculating cells ignoring invalid cells with count function


Example 4: Apply COUNT Function to Calculate Average

Last but not least, you can apply the COUNT function in order to calculate the average. Here, I have calculated the average sales by combining the SUM and COUNT functions. Hence, go through the steps mentioned below.

📌 Steps:

  • Firstly, select cell D13 and insert the following formula.

=SUM(D5:D11)/COUNT(D5:D11)

Apply COUNT Function to Calculate Average

  • After that, press Enter button, and the final output will appear.

calculating average with count


Things to Remember

  • Firstly, #NAME? will appear when trying to use the COUNT function in an older version of Excel.
  • Secondly, #REF! will appear if a COUNT function formula is used between two different workbooks and the source workbook is closed.
  • Finally, you can combine a huge range of functions with the COUNT function.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


Conclusion

These are all the steps you can follow to use the COUNT function in Excel. Overall, in terms of working with time, we need this function for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. Finally, I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.


Excel COUNT Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo