The five COUNT functions in Excel can be confusing, especially for beginners. Microsoft has created 5 different count functions to aid in Counting in Excel. Although most people are familiar with the simple COUNT function, they don’t know how or where to use the rest of them.
In this tutorial, I will guide you on how to use all types of Excel Count Functions.
1. The COUNT Function
The COUNT Function is the most basic and probably one of the most used. It has only one purpose, which is to count cells that contain numbers. Thus, it ignores blank cells, texts, strings, or special characters.
The syntax is
=count(range)
Here is a GIF example of how to use the COUNT function
In the example above I have used the formula =count(D4:G9) to find the numbers in the range. After pressing enter the formula returns 4. Therefore there are 4 numbers in the range.
You can use this function in so many ways. For example, if you have a large spreadsheet with data spread all over. You can use it to coin cells that contain numbers which can be total sales etc.
2. The COUNTA Function
COUNTA function counts all cells that are not blank. This is the best formula for anyone looking to count cells that contain data. Whether it’s Text, Numbers, String, or special characters COUNTA will count all.
Syntax
=COUNTA(range)
Here is a GIF example showing how to use the COUNTA function in Excel
3. The COUNTBLANK Function
As the name suggests the COUNTBLANK Function counts the blank cells. It is the opposite of COUNTA.
The Syntax for COUNTBLANK is
=COUNTBLANK(range)
4. The COUNTIF Function
For more advanced counting in Excel, you will need to use the COUNTIF function. It is a combination of COUNT and IF functions.
The syntax for COUNTIF function is
=COUNTIF(range,condition)
This means it will count cells in the provided range only if a certain condition that you have specified has been met.
For example, you can count cells that contain values that are less than 50 using the following formula.
=COUNTIF(D4:G9, “<50)
Excel will return the number of cells that contain values less than 50. If no cells contain values less than 50 then it will return 0.
5. The COUNTIFS Function
Lastly, the COUNTIFS function in Excel works in a similar way to COUNTIF but with a twist. COUNTIFS counts the number of cells depending on multiple conditions. Here is the syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Criteria_range 1: This refers to the first range of data that the function will scan through
Criteria1: These are the conditions that will be tested against the values. It can be a cell reference, expression, text or number defining the the cells to be counted.
criteria _range2: This is an optional additional range
Criteria2: Refers to additional criteria for the additional criteria range 2 and so on.
For example, I want to calculate the products that are in stock but have not recorded any sales.
Here is a GIF showing how to use COUNTIFS
Frequently Asked Questions
1. What are the 5 COUNT Functions in Excel?
The 5 COUNT functions in Excel are COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS.
2. How to count all cells in a range in Excel?
To count all cells in a range, you need to combine to COUNT functions. The formula to count the number of cells in a range is =COUNTA(range) + COUNTBLANK(range). The first function counts all cells with data while the second counts the blank cells.
3. What is the difference between COUNTIF anf COUNTIFS in Excel?
The difference between COUNTIF and COUNTIFS in Excel is the number of conditions. COUNTIF evealuetes one condition, while COUNTIFS evaluates more than one condition.
4. How to count cells that contain Text in Excel?
To count cells that contain Text only use the formula =COUNTIF(range, criteria). The criteria in this case will be =COUNTIF(range,”*”). Take a look at the image below showing the function in action.
In the above example, we have used =COUNTIF(D4:G9,”*”). D4:G9 is the range which we wish to count cells that contain text. You can also count cells that contain specific text as explained in detail on this post.
