What are the 4 count functions?

This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria.

As everyone knows, Excel is all about storing and crunching numbers. However, apart from calculating values, you may also need to count cells with values - with any value, or with specific value types. For example, you may want a quick count of all items in a list, or the total of inventory numbers in a selected range.

Microsoft Excel provides a couple of special functions for counting cells: COUNT and COUNTA. Both all very straightforward and easy-to-use. So let's take a quick look at these essential functions first, and then I will show you a few Excel formulas to count cells that meet certain condition(s), and clue you in on the quirks in counting some value types.

Excel COUNT function - count cells with numbers

You use the COUNT function in Excel to count the number of cells that contain numerical values.

The syntax of the Excel COUNT function is as follows:

COUNT(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.

In Excel 365 - 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 values.

For example, the following formula returns the total number of numeric cells in range A1:A100:

=COUNT(A1:A100)

Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.

Using COUNT function in Excel - things to remember

Below are the two simple rules by which the Excel COUNT function works.

1. If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
2. If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like "5").

For example, the following COUNT formula returns 4, because the following values are counted: 1, "2", 1/1/2016, and TRUE.

=COUNT(1, "apples", "2", 1/1/2016, TRUE)

Excel COUNT formula examples

And here are a few more examples of using the COUNT function in Excel on different values.

To count cells with numeric values in one range, use a simple count formula like

=COUNT(A2:A10)

The following screenshot demonstrates which types of data are counted and which are ignored:

To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:

=COUNT(B2:B7, D2:D7)

Tips:

• If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.
• If apart from numbers, you also want to count cells with text, logical values and errors, use the COUNTA function, which leads us right to the next section of this tutorial.

Excel COUNTA function - count non-blank cells

The COUNTA function in Excel counts cells containing any value, i.e. cells that are not empty.

The syntax of the Excel COUNTA function is akin to that of COUNT:

COUNTA(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges where you want to count non-blank cells.

For example, to count cells with value in range A1:A100, use the following formula:

=COUNTA(A1:A100)

To count non-empty cells in several non-adjacent ranges, use a COUNTA formula similar to this:

=COUNTA(B2:B10, D2:D20, E2:F10)

As you can see, the ranges supplied to an Excel COUNTA formula do not necessarily need to be of the same size, i.e. each range may contain a different number of rows and columns.

Please keep in mind that Excel's COUNTA function counts cells containing any type of data, including:

• Numbers
• Dates / times
• Text values
• Boolean values of TRUE and FALSE
• Error values like #VALUE or #N/A
• Empty text strings ("")

In some cases, you may be perplexed by the COUNTA function's result because it differs from what you see with your own eyes. The point is that an Excel COUNTA formula may count cells that visually look empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.

In other words, the only cells that the COUNTA function does not count are absolutely empty cells.

The following screenshot demonstrates the difference between Excel COUNT and COUNTA functions:

For more ways to count non-blank cells in Excel, check out this article.

Tip. If you just want a quick count of non-blank cells in a selected range, simply have a look at Status Bar at the bottom right corner of your Excel window:

Other ways to count cells in Excel

Aside from COUNT and COUNTA, Microsoft Excel provide a few other functions to count cells. Below you will discuss 3 most common use cases.

Count cells that meet one condition (COUNTIF)

The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:

COUNTIF(range, criteria)

In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.

For example, to count how many cells in range A2:A15 are "Apples", you use the following COUNTIF formula:

=COUNTIF(A2:A15, "apples")

Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:

For more information, please see How to use COUNTIF in Excel.

Count cells that match several criteria (COUNTIFS)

The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:

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

The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010 - 365.

For example, to count how many "Apples" (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:

=COUNTIFS(A2:A15,"apples", B2:B15,">=200")

To make your COUNTIFS formula more versatile, you can supply cell references as the criteria:

You will find plenty more formula examples here: Excel COUNTIFS function with multiple criteria.

Get a total of cells in a range

If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:

=ROWS(range)*COLUMNS(range)

For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:

=ROWS(A1:D7)*COLUMNS(A1:D7)

Well, this is how you use the Excel COUNT and COUNTA functions. Like I said, they are very straightforward and you are unlikely to run into any difficulty when using your count formula in Excel. If someone knows and is willing to share some interesting tips on to how to count cells in Excel, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

Excel is a widely used data management tool that helps us analyze and interpret data. It is underappreciated for the wide variety of tasks that can be performed on the application. Excel has a versatile library of built-functions, making it a very powerful tool. Hence making it a popular one too. 

In this article, we will be discussing the function COUNT in Excel.

As the name suggests, the ‘COUNT’ function helps us count cells in Excel. But that’s not it! There are five variations of COUNT in Excel, which helps us perform numerous counting tasks that we will be exploring in this article.

The COUNT function is generally used to count the number of cells in Excel or array of numbers.

Example: To count the numbers between A1 and A20, you may enter the following formula: =COUNT(A1:A20). For example, if the range contains three cells containing numbers, the result is 3.

There are two different ways to use COUNT in Excel. The syntax is as follows:

COUNT(value1,[value2],…)  

The parameters of the COUNT function include comma-separated values. This function returns the total count of numeric values specified.

Usage:

Here we specify multiple values as the parameter and the COUNT function finds the total count of values in it.

NOTE: This function works only on numeric data. Hence, it will not find the count of text values if specified.

COUNT(range) 

This function is used to count the number of cells in a specified range. It can be used to quantify data.  

As we can see, using =COUNT(A2: A6), we get the count of cells in the range A2 to A6. This function works only on numeric data.

Now that we have understood how to use the function COUNT in Excel let’s look at the next function - COUNTA. 

This function counts cells with data, i.e., non-empty values within a range. The syntax is as follows:

=COUNTA(value1,[value2],…)

The values can be any range too. For example A1:A10

Usage:

As shown above, =COUNTA(E2: E6) counts cells with data(non-empty cells) in the range E2 to E6.

COUNT calculates the count of cells with numeric values. In comparison, COUNTA finds the count of cells with non-empty data.

As the name implies, COUNTBLANK is a simple function that counts the number of blank or empty cells. The syntax is as follows:

=COUNTBLANK(range)

The COUNTBLANK function works on a specified range as a parameter.

Usage:

Looking at the example shown, COUNTBLANK helps count cells that are left blank/empty. We can use this function to find the entries that are left blank.

The COUNTIF function counts cells that match specific criteria. The syntax is as follows:

 =COUNTIF(range,criteria) 

The two arguments here are:

• Range- which can be specified as a range of cells in Excel. For example B1: B6
• Criteria- a condition on the function. For example: “>50”, A1

This function works on the specified range, counting the cells that match the criteria or condition. 

Usage:

• COUNTIF can be used to match the criterion with a string.

For example: 

We are using the formula =COUNTIF(C2: C6, “Tianjin”) to check how many locations match the city “Tianjin”.

• COUNTIF can be used to count cells with text-

Counting cells that only contain text is simple. We use ‘*’ as the condition.

The ‘*’ or asterisk symbol helps find any number of characters. In the following demo, you will see how the wildcard ‘*’ matches any number of characters.

The function =COUNTIF(D2: E12,”*”) executes over the range D2 to E12 and counts cells that contain text.

If you want a partial match, you can use wildcard characters to count cells that contain a particular word or letters. Look at the example below:

The formula =COUNTIF(C2: C12, “*HUBEI*”) counts the cells that have the value “HUBEI” in the range C2 to C12.

NOTE: Here, the asterisk symbol helps find cells with any sequence of leading or trailing characters. Also, this does not apply to boolean values.

• COUNTIF can be used to count cells with the help of logical operators: Greater than, equal to or less than.

For example: 

=COUNTIF(E2:E6,">=60")

Here, we have used the COUNTIF function to find the number of people over the age of 60 affected by COVID. We have taken the range of ages from cells E2 to E6. The criteria being that the value must be greater than or equal to 60.

NOTE: The criteria MUST be specified within quotes.

Similarly, you can use the following logical operations.

Logical operation	Formula Example	Description
Less than “<”	=COUNTIF(C1:C10,”<20”)	Count if the values in cells are lesser than 20
Less than or equal to “<=”	=COUNTIF(C1:C10,”<=20”)	Count if the values in cells are lesser than or equal to 20
Greater than “>”	=COUNTIF(C1:C10,”>20”)	Count if the values in cells are greater than 20
Greater than or equal to “>=”	=COUNTIF(C1:C10,”>=20”)	Count if the values in cells are greater than or equal to 20
Equal to “=”	=COUNTIF(C1:C10,”=20”)	Count if the values in cells are equal to 20
Not equal to “!=”	=COUNTIF(C1:C10,”<>20”)	Count if the values in cells are not equal to 20

This function is a plural counterpart of the COUNTIF function. It calculates the count of cells upon meeting multiple criteria.

The syntax is as follows:

• =COUNTIFS(range1, criteria1,[range2, criteria2]…)

Here, range1 maps to the criteria1, range2 maps to criteria2, and so on.

The range1 and criteria1 are required arguments while the rest in square brackets are optional.

• Range1 will be the range of cells to which the first condition (criteria1) will be applied.
• Criteria1 defines the condition for the function that will work on Range1. The criteria can be any number, string, expression, or it can also be a cell reference. For example, “>=20”, ”China” or A1.
• Similarly, [range2, criteria2] defines another set of range and its respective criteria to be met. They follow an ‘AND’ logic.

Multiple range-criteria sets can be added, and they must be comma-separated.

We will demonstrate the usage as follows.

The formula =COUNTIFS(B2:B12,">2/1/2020",D2:D12,"France") is used on two criteria. It checks if the reporting date is after 2/1/2020 AND if the country of the deceased individual is France.

NOTE: The COUNTIFS function follows AND logic. It counts cells only if both or ALL conditions are true. 

More About COUNTIF

There are various numbers of tasks that can be performed using COUNTIF and COUNTIFS.

• If you have to find the number of times a value occurs in a range:

You can use =COUNTIF(A1: A10, A1). This will be demonstrated below.

Using =COUNTIF(F2: F12, F2), we find the total count of cells where the cell containing “male” gender has been repeated.

• Let’s say we want to find out if the data contains unique entries or not.

We can do so by using an IF with COUNTIF. Look at the following example.

The COUNTIF function executes first and finds the count of cells within the range A2 to A12, where the value in cell A3 (which is 2) has appeared. If the count is more than 1, duplicate values exist, and the result will be displayed as “DUPLICATE”. Else it displays “UNIQUE”. 

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

We hope this article has given you a strong understanding of how various functions are used to COUNT in Excel. The function may seem to perform simple calculations. But when you combine them with other Excel functions, you will be amazed by how powerful Excel is in getting meaning out of massive datasets.

Do you have any questions related to this article? If so, please mention it in the comments section and our experts will get back to you at the earliest. 

You can also go the extra mile and enhance your Excel skills by enrolling in the Business Analytics with Excel Free Course offered by Simplilearn.