Excel Count Functions │ 5 Practical Functions in Excel

When working with Excel, there will be times that you need to know how many cells are containing data, how many are empty, or how many meet a specific condition. In these cases, you will need the count functions.

Microsoft has provided five variations of the count function: COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.

In this blog, we will introduce all of the count functions in Excel and explain when to use them one by one.

COUNT Function

The COUNT function is the first member of the count family. It returns the number of cells containing numeric values within a range or array. Using the COUNT function, you can understand how many numbers, dates, and even text representations of numbers you have.

The syntax of the function is as below:

COUNT(value1, [value2], …)

Where value1 is required, but the next arguments are optional. The arguments can be cell references or a range of cells.

Helpful Notes

• The COUNT function takes up to 255 arguments.
• The function counts logical values and text representations of numbers only if you type them directly in the list of arguments.

Example: Look at the following image to see how it works.

As you can see, the COUNT function counts the cells including dates.

COUNTA Function

The second member of the count functions in Excel is COUNTA. If you want to know how many cells in your spreadsheet contain data of any type, you can use the COUNTA function. It even counts cells that contain empty texts “” and error values. In other words, the function gives you the number of nonblank cells,

The syntax of the function is just like the COUNT function.

Example: The image below indicates how it works:

In this example, we have counted the cells containing data of three types using the COUNTA function.

COUNTBLANK Function

So far, we have introduced the functions that count cells with values. If you need to know how many cells are empty, the COUNTBLANK function will do the job. It does the exact opposite of what the COUNTA function does. It gives you the number of blank cells.

The syntax of the function is like the two previous functions.

Example:

COUNTIF Function

If you want to count the number of cells that meet a certain condition, the COUNTIF function is the solution. Basically, the COUNTIF function is a combination of the COUNT and IF functions.

The syntax of the function is as below:

COUNTIF(range, criteria)

Where “range” is the range of cells that you want to see and “criteria” is the condition that determines which cells should be counted.

Helpful notes

• You can use numbers, text strings, cell references, logical operators, and wildcards (?, *, and ~) as the criteria. Wildcards are used for partial matching. It means that your cells may include several words or characters, but you are looking for a specific part of them.
• When using text strings in criteria, you must put them in double quotes.
• When using cell references in criteria, do not enclose them in quotes.

• If you try to use the function to match strings longer than 255 characters, it will return a wrong result.
• The COUNTIF function is not case-sensitive. It means that it cannot distinguish between capital letters and lower-case ones.

Now, let’s make it clearer with a couple of examples:

Example: In the example below, the COUNTIF function gives the number of products with a price of more than $1000

In the following example, you can see that the COUNTIF function can be applied to cells with text strings, but it’s not case sensitive.

COUNTIFS Function

As we discussed above, you can count cells with a certain condition using the COUNTIF function. But if you want to count cells with multiple conditions, it doesn’t work. In these cases, you have to use the COUNTIFS function.

The syntax of the function is as below:

COUNTIFS (criteria_range1, criteria1, [criteria_range2], [criteria2], …)

Criteria_range1: Is the first range to check.

Criteria1: Is the first criteria to apply to searching range1.

Criteria _rang2, criteria2, …: Are the additional ranges and their corresponding criterion to check and count.

Helpful notes

• When using any condition other than numbers, you must enclose it in double quotes.
• Each additional range that you add must have the same size (equal number of rows and columns) as the first one. Otherwise, it returns the #VALUE error.

Example: In this example, the COUNTIFS function counts the cells in B2:B11 that are greater than 1500, and at the same time cells in D2:D11 that have the alphabet “a.” Note that the number that it returns is the cells in each row that meet these conditions.

Sum up

In this blog, we introduced the functions in the count family, which help you find out how many cells are containing data, how many are empty, or how many meet one specific or multiple conditions.

The table below summarizes the use of each of these functions.

Function	Use
COUNT	It returns the number of cells containing numeric values.
COUNTA	It returns the number of cells containing any type of data.
COUNTBLANK	It returns the number of empty cells.
COUNTIF	It returns the number of cells that meet a specific condition.
COUNTIFS	It returns the number of cells in multiple ranges that meet multiple conditions.

Also, you can download the example file used in this blog to practice.

 

You can connect with us and make inquiries to get more Excel Support Services. Reduce cost, accelerate tasks, and improve quality with Excel Automation Services.

FAQ