Excel Count Functions │ 5 Practical Functions in Excel

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.

How to use COUNT function in Excel

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:

How to use the COUNTA function in Excel” tag=” Excel, blo

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:

The use of COUNTBLANK function in Excel

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

How to count values greater than a value by the COUNTIF function in Excel

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.

How to apply the COUNTIF function to cells with text string values in Excel

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.

COUNTIFS function with two different conditions in Excel

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.

FunctionUse
COUNTIt returns the number of cells containing numeric values.
COUNTAIt returns the number of cells containing any type of data.
COUNTBLANKIt returns the number of empty cells.
COUNTIFIt returns the number of cells that meet a specific condition.
COUNTIFSIt 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

Can I use cell references in the criteria argument in COUNTIF and COUNTIFS functions?

Yes, to include cell references together with comparison operators in the criteria argument in COUNTIF and COUNTIFS functions, you should enclose the operator in double-quotes and put the “&” operator before the cell references. For example:

=COUNTIF(B2:B11,”>”&E3)

How do I count unique entries in Excel?

To count unique values in Excel, one way you can use is the combination of the COUNTIF, IFERROR, and SUM functions. To give an example, assume that you want to count unique values in the range of (A1:A10). Type the formula below in the cell that you want to see the result and then press Ctrl+Shift+Enter:
=SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),”empty”))

How to count cells that contain only text?

Assume that you want the number of cells containing only text in the range of A1:A10. Type the formula below and press Enter:
=COUNTIF(A1:A10,”*”)

Subscribe to our Newsletter

Share this post with your friends

Leave a Reply

Your email address will not be published.