How to Use COUNT Function in Excel (With 5 Examples)

Overview of COUNT function

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. This COUNT function is used to get the number of entries in a number field that is in a range or array of numbers. This article will share the complete idea of how the COUNT function works in Excel autonomously and then with other Excel functions.

Download the Practice WorkBook

COUNT Function in Excel (Quick View)

Overview of COUNT function

Excel COUNT Function: Syntax & Arguments

Excel COUNT Function Syntax & Arguments

Summary

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

Syntax

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

Arguments

Argument Required or Optional Value
value1 Required Pass an item, cell reference, or range.
[value2] Optional Pass an optional item, cell reference, or range.

Note: 

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

How to Use the COUNT Function in Excel (5 Examples)

Example 1: Count Numbers in the Given Range Using COUNT Function

Using this COUNT function, we can easily count any range of numbers. For showing the process let’s assume we have a dataset of some Foods with their name, category, date, and sales. Now we will count the number of sales by counting the sales cells.

Count Numbers in the Given Range Using COUNT Function

Step 1: Enter the formula in cell G6 and press Enter

=COUNT(E4:E15)

 Enter formula using COUNT function

Example 2: Count Non-empty Cells Using COUNT Function

As we know that the COUNT function does not count any empty cells, so in our dataset if we have an empty cell this function will ignore the call. Let’s consider our dataset as below:

Count non-empty cells with COUNT function

Step 1: Enter the formula in cell G6 and press Enter

=COUNT(E4:E15)

Enter formula using COUNT function

Observation

As we can see from the above picture the count function is ignoring the empty cells. It has counted the cells which are containing the number values.

Example 3: Count the Number of Valid Dates Using COUNT Function

Another feature of the COUNT function is it will only count the valid dates. For this process let’s assume the dataset like this:

Count number of valid dates using COUNT function

Step 1: Enter the formula in cell G6 and press Enter

=COUNT(D4:D15)

Enter formula using COUNT function for valid dates

Observation

As we can see from the above picture the count function is ignoring the cells which are containing invalid dates like 1st June 2021, 18/5/2021, etc. It has counted the cells which are containing the valid dates.

Example 4: Increase Count Using COUNT Function 

This COUNT function allows us to increase any count as per our requirements. Let’s want to increase the number of sales counts by 1 or any number. Then we can take the help of the COUNT function.

Step 1: Enter the formula in cell G6 and press Enter

=COUNT(E4:E15,1)

Formula Breakdown

  • Here the first COUNT function counts the number of elements in E4:E15 in this range which is 12.
  • Then as we have passed 1 as the second argument the total count will be increased by 1 and the final count will be 12+1 =13. Here in the second argument, we can pass any number like 2,5,100. All of them will return the same output as we are not considering the value of the number, we are considering the appearances of the number.

Increase Count Using COUNT Function 

Example 4: Count Ignoring Invalid Cells Using COUNT Function

At the time of counting, the numbers COUNT function ignores invalid cells. Let’s assume in our dataset in sales columns some of the rows are containing text or string. We want to calculate the number of sales by ignoring those invalid sales.

Count Ignoring Invalid Cells Using COUNT Function

Step 1: Enter the formula in cell G6 and press Enter

=COUNT(E4:E15)

Enter formula using COUNT function

Example 5: Calculating Average Using COUNT Function Formula

Let’s say we want to calculate the average sales after 6th June 2021. We can do this using a formula with the COUNT function.

Calculating Average Using COUNT Function Formula

Step 1: Enter the formula in cell G6 and press Enter

=AVERAGE(OFFSET(E4,COUNT(E4:E15),0,-7))

Formula Breakdown

  • Firstly, COUNT(E4:E15) counts the number in this range.
  • Then OFFSET function helped in formulating dynamic rectangular ranges. Here E4 is our reference, COUNT(E4:E15) is the rows, 0 is defined as columns and lastly, -7 is used to extend the rectangular range up “backward” five rows to create the range E10:E15. You wanted to explore more about this function, you can check this link
  • Then finally, the AVERAGE function calculator the average of the selected data. You can check this link to know more about this Excel function.

Enter formula using OFFSET, Average and COUNT function

Things to Remember

Common Errors When they show
#NAME It will appear when trying to use COUNT in an older version of Excel.
#REF! It will appear if a COUNT function formula is used between two different workbooks and closed the source workbook.

Conclusion

This is all about the COUNT function and its different applications. 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. If you have any other method of utilizing this function, then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo