How to use Excel Count Functions

Overview of Excel Functions

Excel count functions or very handy and help you in a great way to deal with their assets when it comes to Counting stuff inside Excel. In this article, we are going to talk about five different count functions of Excel.

Types of Count Functions in Excel

Excel COUNT
Excel COUNTA
Excel COUNTBLANK
Excel COUNTIF
Excel COUNTIFS

Download Practice File

How to use Excel Count Function

Excel count function comes in very handy when you want to count numbers from a given range. Please note that this function will count only the numbers inside any given range. If there is any cell that contains non-numeric values such as text or number is not formatted as numbers that won’t be counted.

In this example, we are trying to count the numbers of A3 to F12 cells. To begin calculation, our function may look something like this:

=Count(A3:F12)

Once you press enter after entering this function, you might notice that it brings you of value 38 that is the number of numbers available in that range. It completely ignores the text values and blank cells. In case you desire to count all the cells that are not blank in that range, we may have to use a different count function in Excel. Let us discuss that one in the next section.

How to use Excel COUNTA Function

In case you desire to count everything in a given range, excluding the blank cells irrespective of the type of value the cell contains Excel CountA function will come in very handy. Do not worry about how to use the Excel COUNTA function. We are here to help you understand how to use the Excel COUNTA function. To perform the calculation on the data set we have in this example, we will use COUNTA as follows:

=COUNTA(A3:F12)

The COUNTA function will count everything in that range, excluding the cells which are left blank, returning the value as 58. What if we desire to count the empty cells? We will need to use Excel COUNTBLANK function. Let us discuss the Excel CUONTBLANK function in the next section.

How to use Excel COUNTBLANK Function

The Excel COUNTBLANK function has the capability of counting blank cells in a given range. With the help of Excel COUNTBLANK function, you will be able to find the exact number of empty cells in any given range in your Excel workbooks or worksheets. In our case, the Excel COUNTBLANK function will look something like

=COUNTBLANK(A3:F12)

The function will return us 2 as value as there are only two cells that are blank in the given range. Now let us discuss the other to count functions that are logical in nature in the next sections.

How to use Excel COUNTIF Function

The Excel COUNTIF Function has a unique way of functioning. It takes two arguments to perform the calculations. The first argument is the range, and the second one is the criteria.

Range: This is where you want Excel to look for the values to count.
Criteria: This is the criteria, if met, the value should be considered for counting.

In our example, we would like Excel to count in the range of results that is in column F and count only where the values are listed as Passed. Here is how we will write the function to perform this calculation.

=COUNTIF(F3:F12, “Passed”)

In this function, we are clearly outlining the range F13 to F12, asking Excel to look only in that specific range to count all the instances where the value is Passed. Imagine you have a situation where you need to set multiple criteria to count. The Excel COUNTIFS function plays a vital role in a situation where we need to consider multiple criteria and ranges. Let us discuss the Excel COUNTIFS function in the next section of this article.

How to use Excel COUNTIFS Function

Excel COUNTIFS Function works very similarly to the Excel COUNTIF Function; however, it takes multiple parameters for arguments to perform the calculation. In our case, we are asking Excel COUNTIFS Function to look for two different criteria in two different ranges.

We want Excel COUNTIFS Function to look inside the results range and consider the instances where the value is listed as Passed for counting. In the second set of Excel COUNTIFS Function, we are asking the function to scan in the range of the average column and consider any value that is greater than 70 for counting. Only the instances where these two criteria meet i.e., the value you should be Passed, as well as, in the Results column, it should be greater than 70 in the average column to be considered for counting. Our Excel COUNTIFS Function may look something like the following:

=COUNTIFS(F3:F12, “Passed”, E3:E12, “>70”)

Excel will first look into the range F3 to F12 and consider all instances where the value is is listed as “Passed.” Then it will move to E3 to E12 range and consider any value that is greater than 70. Since we do not have a reference cell to compare the values which are greater than 70, we have provided that under double quotations. This will make the reference check independent and start comparing it with each cell in a given range. The value that will be returned in this case is 3 as there are only three instances where the average is more than 70, and the Result is Passed.

Excel Count functions Video Tutorial

In case you want to watch and learn, here is a quick video demonstration of the Excel count functions.

Click on the following button to download the file used in the demo to practice along with us.

Download Practice File

Excel Learning Series – TOC

We hope you find this article useful. In case you are stuck and or need help, feel free to drop a comment below or submit your question. Do share it further and add your feedback about the article in the comments section.

My Knowledge Portal

Learning made easy

How to use Excel Count Functions

Overview of Excel Functions