Count Function in Excel: A Comprehensive Guide

Count Function In Excel

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.



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.
  1. 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.
  2. 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”)
  3. 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.

Related posts


Leave a Comment