Unleash the Power of the COUNT Function in Excel

This guide will explain in more depth how to use the COUNT function in Excel.

You need to know that Counting and Adding in Excel are different things.

To calculate it, you can use the COUNT function and its variations (such as counting how many cells contain a number).

Meanwhile, for addition, you can use the SUM function and its variations (such as adding several numbers).

So, before starting, it’s a good idea to first know what the COUNT Excel formula means.

What is the COUNT Function in Excel?

The COUNT function in Excel is a formula used to count the number of cells within a specified range that contain numeric data. This data can include numbers, dates, times, currency values, percentages, fractions, decimals, and scientific notation.

In Excel formulas, arguments can be either single cells or ranges of cells, located on the same Worksheet or different Worksheets.

Note: The COUNT formula is not the same as SUM. The SUM formula will add all the numeric data (numbers, dates, etc.) in all cells. Meanwhile, the COUNT formula only counts how many cells contain numeric data.

To understand the use of the COUNT Formula as a whole, let’s start by understanding the rules for writing the COUNT Formula. Then practice how to use it. Are you ready? Let’s start…

COUNT Excel Formula Breakdown

Just like other Excel formulas and functions, the COUNT Excel Formula has specific writing rules, also known as syntax, that you need to understand. Here’s the default way to write the COUNT formula:

Syntax of COUNT Function in Excel:
=COUNT(value1,[value2],…)

As you can see, the COUNT function has 3 arguments (there can be more depending on your data) which can be mandatory or optional as follows:

1. value1 (required) can contain a cell/range/other Excel function/formula on the same or different sheet.
2. [value2] (optional) can also contain a cell/range/other Excel function/formula which should not be the same as the value1 argument.
3. … (optional) can be called “and so on”. So if the cells or data ranges are in non-adjacent positions (either on the same sheet or on different sheets), then the argument … can be considered as [value3]. If there are other Cells/Data Ranges, you can add the [value4] argument, etc.

Important! If you don’t know what ranges and cells are, please visit the Range Guide in M Jurnal

After knowing the meaning of each argument, both mandatory and optional, hopefully the following example can provide a detailed explanation of how to use the COUNT function in Excel.

As Excel practice material this time, please download the following Excel file:

How to Use the COUNT Function in Excel with 1 Argument?

As a first example of how to use the COUNT function in Excel, I use data in the range C2:C5, which contains data in the form of numbers and text as shown in the following image:

Then, to count how many cells contain numeric data from that data range, please enter the COUNT function in Cell C6 as follows: =COUNT(C1:C5) and see the results:

The COUNT function returns 3. This means that there are 3 cells in the data range that contain numeric data.

Is it true that the COUNT formula in Excel only counts how many cells contain numeric data?

Let’s try to prove it…

Please change the value in Cell C5 to a number (for example 25) and see the changes in the results of the COUNT function below:

Isn’t that right? The COUNT formula result changes to 4, which means there are 4 cells in the data range that contain valid numbers.

Note: I added the SUM formula in cell C7 to see the difference between the COUNT and SUM formulas. As you can see, the SUM Formula adds up all the numbers from the data Range, namely 11+32+52+25 = 120. While the COUNT Formula counts the number of cells that contain numeric data.

How to Use the COUNT Function in Excel? (2 or More Arguments + Different Sheets)

As explained in the COUNT Function Formula Breakdown section, you can use more than 1 argument in the COUNT Formula. Each argument can be a cell/range of data, either on the same sheet or on a different sheet.

For example, I will use the COUNT function for 3 different sheets which look like the following image:

In this example (specifically on sheet COUNT1), I don’t want to enter data in column D into the COUNT formula. So, the data in columns B and C are close to each other.

Meanwhile, the data in column E (especially cell E3 is located far from columns B and C.

In this example (especially on sheet COUNT1), I only want to count how many cells contain numeric data from the data in columns B, C, and cell E3.

For cases like this, you can enter the range B1:C5 as the first argument value1 in the COUNT formula. Then, enter cell E3 as the [value2] argument in the formula.

Likewise with data in the range A1:A5 on sheet COUNT2 and cell C2 on sheet COUNT3. You can enter them as arguments [value3] and [value4].

To understand it better, please follow how to use the COUNT formula in Excel for more than 1 argument and on different sheets below:

1. Type the COUNT function in cell C6 as follows: =COUNT(.
2. Select the range B1:C5 on sheet COUNT1.
3. Because cell E3 isn’t adjacent to the range B1:C5, add a second argument by typing the formula separation operator – comma , then click cell E3 on sheet COUNT1 so that the formula becomes =COUNT(B1:C5,E3.
4. To add a third argument, type the formula separation operator , then select the range A1:A8 in sheet COUNT2.
5. To add a fourth argument, type a comma , then select cell C3 on sheet COUNT3.
6. Since there are no further arguments, type closing parenthesis ) to end the formula and press Enter. See the results:

Note: If the cells/ranges aren’t adjacent (far apart), then you can only enter them into the formula and separate them from other ranges according to the arguments.

Isn’t that right? Only 7 cells from the data range used in the formula contain numeric data.

Important! To make it easier to enter formulas on different sheets, please use the View Multiple Worksheets feature in Excel. Please visit the Guide to Viewing Multiple Worksheets in Excel at Once.

In-depth Understanding of the COUNT Formula in Excel

A deep understanding of the COUNT formula syntax in Excel can help you to use the COUNT function for various purposes.

So, let’s take the example of the COUNT formula with 2 arguments and different sheets above.

COUNT Function Formula Breakdown:
=COUNT(B1:C5,E3,COUNT2!A1:A5,COUNT3!C2)

Explanation:
Count How Many Cells Contain Numeric Data from cell B1 to cell C5, cell E3, cell A1 to cell A5 on sheet COUNT2, and cell C2 on sheet COUNT3

So… Each argument is treated as an “and”, just like the SUM, AVERAGE, etc. function arguments.

Additionally, if you use the formula for a different sheet, Excel includes the sheet name in the formula argument.

However, it is different if you use Named Range or Table Format as an argument to the COUNT function.

You can create structured COUNT Formula arguments in Excel using a Named Range or Table Format, such as =COUNT(Price, Sales, Discount).

BONUS: COUNT Formula Facts and Examples

Let’s take back the main key in using the COUNT Function in Excel…

The COUNT function in Excel simply counts how many cells contain numeric data.

Rolan Mardani – M Jurnal

So… Let’s explore more deeply the facts about the COUNT function in Excel.

Numerical Data: Date, Time, Currency, Accounting, etc…

FYI, in Excel apart from valid numbers, there are 7 types of Numerical data, such as Date & Time (in any format), Scientific, Currency, Accounting, Percentage, and Fraction. All of these Format Cells also include numeric data.

In addition to the seven main cell formats mentioned above, Excel will also include cells formatted with Custom Number Formats or Special formats, as long as the underlying data is numeric.

As you can see, range C2:C8 uses the Format Number (numeric format), so the COUNT function includes these cells in its results.

However, cells C9 and C10 are formatted as Text, even though they display dates and numbers. Because of this formatting, Excel excludes them from the COUNT function’s results.

Important! Want to expand your knowledge? Learn more about Format Cells in Excel with our guide.

Logical Values (TRUE & FALSE) Used in Formulas

In addition to numeric values, the COUNT function also counts logical values (TRUE and FALSE) that are the result of logical formulas in Excel (such as IF or OR formulas), as well as numeric values that are typed directly into the argument, either with or without double quotes (e.g., “20”).

This applies whether you use the formula in the cell containing the values or directly enter the values into the COUNT formula’s argument.

In the data range A2:A7, only cell A4 contains numeric data (20). Cell A6 contains text data (since the number is enclosed in double quotes “20”).

The cells containing TRUE and FALSE values are the result of a logical formula.

There were three tests that I performed:

First, when I used the formula =COUNT(A2:A7), Excel only counted the cells containing numeric data (A4). You can see the test result in cell A9.

Second, when I used the formula =COUNT(A2:A6,AND(D1=5,E1>10)), Excel also counted the FALSE result of the AND formula. See the test result in cell A10 = 2.

Note: I changed the range from A2:A7 to A2:A6 in the COUNT formula. The AND formula in the COUNT formula returns FALSE, which is the same result as the AND formula I used in cell A7.

Third, When I type the formula results (TRUE or FALSE) from the range along with the numeric values 20 and “20” into the COUNT formula argument, such as =COUNT(FALSE,TRUE,20,TRUE,"20",FALSE), Excel counts all of this data as numeric values. See the result in cell A11 = 6.

Error Message Data Like #DIV/0!, #REF!, etc.

If any of the following Excel error messages, such as #DIV/0!, #REF!, or #N/A, appear in your data range, the Excel COUNT formula will not count those data points.

As shown in the image above, only four cells contain numeric data, and Excel ignores cells containing error messages.

I understand that there are numerous scenarios in which the COUNT function is used in Excel. I hope this guide has addressed your specific issue.

If you’re still unable to find a solution using this guide, please feel free to leave a comment below. I will assist you and we can work together to find a solution.

Next, please use the following Shortcut Guide to improve your skills regarding the SUM and COUNT functions in Excel. Or visit the Excel Guide Center in M Jurnal to learn all about Microsoft Excel.