If you need to count in excel, here you have the COUNT function that is perfect tool for you. The COUNT function is one of popular hacks in Excel. And it is incredibly simple to use. Here we will look at everything you need to know about the COUNT function. And our points are including how to use it, when to use it and some advanced tips.
Table of Contents
1. What is the COUNT function? how to use count function in excel
The COUNT function counts the number of cells in a range. And these should contain numerical values. It can also count the number of cells of different contents, Such as text, logical values or errors. The COUNT function can calculate the total number of values in a range. Or it can determine how many cells in a range meet specific criteria.
2. How to use the count in excel
Using the COUNT function in Excel is easy for all level of users. First, you need to select the cell where you want the result. Now you can write “=COUNT(“. But you need to select the cells for counting. And you should close the parenthesis. And voila, you will see the result.
3. excel count function syntax
The COUNT function has the following syntax for every usage.
=COUNT(value1, [value2], ...)
- value1: It is required. And it is the first value or range of cells to count.
- value2, …: This one is Optional. You can add additional value cells to count.
4. count formula in excel examples
Example 1: Basic COUNT function: how to use count in excel
In case you have cells that contains numerical values. You can count the number of them with values using following formula.
=COUNT(A1:A10)
This formula will count the number of cells in the range A1:A10. And these will contain numerical values.
Example 2: COUNTIF function for how to count cells in excel
Now, we will suppose you have a range of cells with both numerical and text values. If you wanna find cells in the range with a specific text value, it is time to use the COUNTIF function. For example, for finding cells in A1:A10 with text apple, here is your formula below.
=COUNTIF(A1:A10,"apple")
Example 3: COUNTIFS function: What Is Count in Excel
The COUNTIFS can count cells based on multiple criteria. For example, you can count range A1:A10 with text written apple and have a value greater than 5, you can check below.
=COUNTIFS(A1:A10,"apple",A1:A10,">5")
Example 4: COUNTBLANK function
The COUNTBLANK function counts the number of cells in a range that are blank. For example, to count the number of blank cells in the range A1:A10, use the following formula:
=COUNTBLANK(A1:A10)
- In order to count cells based on multiple criteria, you will use the COUNTIFS function.
- For counting cells with either of two criteria, you should use the SUMPRODUCT function with the — (double negative) operator. For example, if you wish to count cells in the range A1:A10 with text apple or orange, here is the formula.
=SUMPRODUCT(--((A1:A10="apple")+(A1:A10="orange")))
- For counting cells not blank, you can use the COUNTA function. For example, if you wanna find non-blank cells in the range A1:A10, you can use below.
=COUNTA(A1:A10)
- To count cells that contain specific text or values, use wildcards in the COUNTIF or COUNTIFS function. If you wish to find ones with text app, you can change it like below.
=COUNTIF(A1:A10,"app*")
6. Common errors with the COUNT function
The most common error with the COUNT function in Excel is using it to count cells with errors. The COUNT function counts cells that contain numbers, text or logical values. If you wanna count cells with errors, you can use the COUNTIF function with the error value after determining this as criteria. In order to count cells having A1:A10with #DIV/0! error, you can do below.
=COUNTIF(A1:A10,"#DIV/0!")
7. When to use the COUNT function
The COUNT function in Excel is useful whenever you need to count cells. And this should be in a range that meet specific criteria. It can calculate the total number of values in a range. Or, you can determine how many cells in a range meet specific criteria.
In this article, we’ve covered everything you need to know about the COUNT function.
9. Questions
-
- The COUNT function counts cells with numerical values, text or logical values. But it does not count cells with errors or are blank. The COUNTA function counts cells that contain any type of value. This is including errors and blanks.
- Can the COUNT function in Excel count cells based on multiple criteria?
- No, it is for one criterion. If you wish to count cells with multiple criteria, you can use COUNTIFS function.
- What is the formula to count cells with specific text in Excel?
- You can use the COUNTIF function with the text value as the criterion. For example, if you want to count cells in A1:A10 with text “apple”, you can use the following formula. =COUNTIF(A1:A10,”apple”)
- Can the COUNT function in Excel count cells that contain errors?
- No, but you can use the COUNTIF function with the error value. Here the criterion will count cells with errors.
You can read How to Control Charts in Excel: A Comprehensive Guide to learning more about Excel. You can also check the other content.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.