Easily learn how to use COUNT function in excel

COUNT function in excel is super useful feature. In this article, we will deep dive into the functionalities of COUNT function in Excel. Learn more..

Did you know that COUNT functions are one of the most commonly used functions in our work and lives? I am confident that you will be able to handle COUNT functions after you review the descriptions and examples in this document. You will learn 4 functions; these functions are COUNT, COUNTA,COUNTBLANK and COUNTIF. In addition, there are 5 more functions that I have applied to the below examples which you can briefly review in this exercise and are explained in more details throughout this document.

What are the COUNT functions?

The COUNT function helps count the number of cells that contain a number or text given conditions in a range. There are functions related to the COUNT functions that count cells with text, words in Excel, and unique values.

Why are the COUNT functions important to learn?

As a basic and commonly used function in Excel, it is important to learn about this function first in order to advance to more complex and difficult functions.

Purpose

– Focusing on how to use the COUNT group of functions: COUNT, COUNTA, COUNTBLANK and COUNTIF

Briefly Leaning about 5 more functions: EXACT,SUMPRODUCT,ISERRORS,ROWS and double-hypen.
Learning what the functions do and how to use them in formulas.
Reviewing functions with examples and following how to use them in a video with a sample file.

Today’s functions

The following functions are what we are going to use in the five examples below:

It is the important to read the instructions and descriptions first before you go through the examples. Please read all content carefully.

Examples

To understand the uses of the COUNT group of functions, here are five practical examples to help you apply and use COUNT functions.

As an example, there is a list from a college which shows that student’s name, program, midterm, final, and total grade. We can apply functions to the list to help sort and summarize data.

First, we will use several basic functions which are COUNT, COUNTBLANK, and COUNTA. You can count the cells that contain numbers with the COUNT function, the number of cells that excluded blanks with COUNTA function, and the number of blank cells with COUNTBLANK function.

Example 1)

Let’s assume that you would like to count how many cells contain numbers in the list.

To do this, you will be using the COUNT function which counts the number of cells that contain numbers. If you select B4:F15 as the value, Excel will return 33 which is the number of cells that contains numbers, and not texts or blanks .

Let’s assume that you would like to count the number of cells that does not contain blanks in a range. The COUNTA function will count cells which has a text, a number, a formula, a symbol, and an error except for blanks.

In the below example, you will be using the COUNTA function. If you select B4:F15 as the value, Excel will return 57 which is the total number of cells which do not include blanks in the range.

Let’s assume that you would like to count the number of cells that are blanks in a range.

In the below example, you will be using the COUNTBLANK function. If you select B4:F15 as the range, Excel will return 3 which is equal to the total number of blank cells in the range.

Second, we will use a function that allows you to count cells with specific conditions. Let’s learn how to use this function with the following example.

Example2)

Let’s assume that you would like to count the number of cells that satisfy a single condition within a range that you select. You can use the COUNTIF function to achieve this. Let’s learn how to use this function with the following example.

Greater& less than

When you would like to count the number of cells that contain a number that is greater or less than a specific number in a range, this can be achieved with the following example.

In this example, there are three ways to determine if a cell is greater than 80. If you would like to find values in a midterm exam range, you can select D4:D15 as a range and select cell I5 which has “>80” in the first way or create a criteria that is your condition such as “>80” or “>”&80 in the second and third way. Excel will return 5 which is equal to the number of cells which have a number greater than 80 on a midterm exam.

Between

If you would like to count between 70 and 90(70< ? <90) in a midterm exam range, you need to subtract less than 70 from less than 90. For another example in this way, if you would like to count between 40 and 80, then you can subtract less than 40 from less than 80.

Equal

When you would like to count the number of cells which are equal to a specific number, as in this example, you can select “I9” as the criteria, and then you can easily count values.

Not equal

If you do not want to count the exact number, then you can count cells which are greater than or less than an exact number using “<>”. Excel will exclude a criteria selected. If the value in cell I10 is “100”and you want to count the number of cells that are not equal to 100, the criteria will be “<>”& I10, and it will return 10 which means cells not equal to 100.

Third, we will apply the COUNTIF function when you would like to count texts.

Example3)

Condition

To count the number of cells with a unique condition, you can use the COUNTIF function like this example. If you have more than two conditions, you can add a second COUNTIF function with the second criteria.

Not case sensitive

The COUNTIF function is not case sensitive. In this example, the word ”Accounting” and “accounting” can be counted in any uppercase/lowercase.

However, if you want to count case sensitive cells, you can use the EXACT function along with the SUMPRODUCT function. Since the EXACT function returns True or False, you can use a double-hypen that converts the true& false values into 1& 0. After that, you can add up the SUMPRODUCT function to return the sum of products. Let’s follow the instruction below.

You can see the FALSE after using the EXACT function because it returns true and false. After adding the double-hypen, you can see that the value returns O because the double-hypen converts true &f false into 1&0. Finally, as adding up the SUMPRODUCT function, you can see the value 2 which is the number of cells that contain certain text “Accounting” in a case-sensitive manner.

Asterisk (*)

You can count texts regardless of any condition using the COUNTIF function by using an asterisk. An asterisk(*) means “one or more characters”, so it can replace any number of characters.

You can specify characters starting with or ending with a certain character.

Therefore, if I want to find text ending with “ce”, then you can use an asterisk like “*ce” as a criteria. In this example, you can select the range and click “*ce” as criteria, then you will have value 3 as a value which means there are three number of cells that contain “ce” at the end of character.

Fourth, what if we want to find or count the number of errors in a list? You will be able to review how to count the number of cells that contain errors in a range in the three different ways in the examples. Here are some functions that we can use to figure it out.

Example4)

Long way

In this example, we are using the COUNTA, COUNT, and COUNTIF function as it allows you to count all of the cells that are not empty, and you can subtract cells that contain numbers and texts. Then, you can finally find the number of cells that contain errors.

Therefore, you can use the COUNTA function and subtract cells using the COUNT function which counts cells that contain numbers and the COUNTIF functions by using asterisk to count texts. You can finally find and count errors. Even though this method is a long way to count this, it is good to know.

One type of Errors

Simply, you can select J6 showing an error like “#NAME?” in a criteria in this example.

Better way

There is a better way to count Errors which using the ISERROR function. The ISERROR function checks wherever a value is an error or not and returns true or false. Then, you can use the double-hypen that converts the true& false values into 1& 0 as you already learned in another example. Finally, you can add up the SUMPRODUCT function to count errors in a range.

You can see the TRUE after using the ISERROR function because the function returns true and false. After adding the double-hypen, you can see that the value returns 1 because the double-hypen converts true &f false into 1&0. Finally, using the SUMPRODUCT function, you can see the value 3 which is the number of cells that contain the error “#NAME?” in a range.

Fifth, we will use an example that will show you how to find a percent within a range.

Example5)

We have a survey that shows if students were satisfied with the class and their grade. You can see “Yes” or “No” as an answer.

In this example, you can use the COUNTIF function to count the number of cell that contain certain condition “Yes” to show if students are satisfied with their grade. To count how many students were satisfied with their grade, you can select the range of the grade (D5:D16), and type “Yes” and it will return 6.

Also, you can apply functions to find a percentage. You can use two functions to achieve this: the ROWS function which include blanks and the COUNTA function which exclude blanks. Therefore, Excel returns 0.5 when you divide by the ROWS function and 0.6 when you divide by COUNTA function.

Sample file that you can download and follow the examples what you have reviewed in this lesson.