COUNT Formula in Excel: and more!

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

The COUNT formula in Excel is an invaluable tool that makes working with data much more manageable. It helps you determine the number of cells containing numbers within a specified range or array. This function can be handy when working with large datasets and extracting specific information, such as the total number of entries or comparing datasets using various criteria.

You may also come across variations of the COUNT function, like COUNTIF, COUNTIFS, and COUNTA, which offer even more flexibility in analyzing your data. And I’ll touch on those shortly, but let’s get you up to speed on COUNT first.

What is the Count Function?

The COUNT function allows you to easily count the number of cells that contain numbers in a specified range or array. To utilize the COUNT function, you simply need to supply the appropriate arguments, such as a range of cells or individual cell references

For instance, the formula =COUNT(A2:A8) will return the count of numeric values within the range A2 to A8. The value returned is 5. 

example of count formula in excel

Cells A4 and A8 have a text value which are not counted. And even though cells A6:A7 have different formats, Excel knows to count these numeric values.

The COUNT function is particularly helpful in situations where you need to count the occurrence of specific numbers or when analyzing data sets with many numeric entries.

With practice, the COUNT function and its related functions will become powerful allies in your Excel toolbox, making your data analysis tasks more efficient and accurate.

How Do You Use the Count Formula in Excel? More Examples

When working with a large number of numbers or values, it simplifies finding the number of cells with numerical values in a specified range. To start using the COUNT formula, like before, enter =COUNT(value1, value2, …) in any cell.

Let’s say you have a list of data in cells A2:A20. To count the non-blank cells in that range, input =COUNT(A2:A20) in a separate cell, say C2. Press Enter, and Excel will display the number of cells containing numeric values in the range A1 and ignoring the blank values.

example of count formula in excel 2

Here’s a tip: You can use cell references, like A1, or a combination of hardcoded values and cell references as arguments in the COUNT formula. This allows you to calculate various count scenarios, such as counting the number of times a specific value or condition is met with variations of the COUNT function. 

For another example of how COUNT works and what truly constitutes a number, look at the example below.

example of count formula in excel 3

Why does the formula only return eight numeric values? Obviously, the blanks and text strings are ignored, even if there are numbers within a text string. But shouldn’t there be nine? Well, no.

Cell A10 looks like a number value, but by inspecting the formula bar, you see that an apostrophe was added to show the beginning zero. By doing that, it converts the number to a text string.

example of count formula in excel 4

Other Useful Count Formulas in Excel

In Excel, besides the basic COUNT function, you’ll find a few other count formulas that can help you analyze your data more effectively. These include COUNTIF, COUNTIFS, COUNTBLANK, and COUNTA, each of which is useful for a specific counting task.

COUNTIF in Excel

The COUNTIF function is used when you want to count the cells within a range that meets specific criteria. The syntax is =COUNTIF(range, criteria). For example, if you want to count the numbers greater than 100 in cells A2:A20, you’d use the formula =COUNTIF(A2:A20,”>100″).

COUNTIF EXAMPLE IN EXCEL

COUNTIFS in Excel

COUNTIFS is an extension of COUNTIF. It allows you to apply multiple criteria on different ranges. The syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…). For instance, if you wish to count the numbers greater than 100 in the previous cells, and the corresponding Team Name in column B that equals “Red,” your formula would be =COUNTIFS(A2:A20,”>100″,B2:B20,”Red”).

COUNTIFs EXAMPLE IN EXCEL

COUNTA in Excel

With COUNTA, you can count the non-blank cells in a range. Its syntax is =COUNTA(value1, [value2]…). If you want to count the non-empty cells in the range A2, enter =COUNTA(A2:A20). This function computes how many cells have any data, including text, numbers, and other content. But it excludes the blank cells.

COUNTa EXAMPLE IN EXCEL

COUNTBLANK in Excel

Lastly, the COUNTBLANK function does the opposite, enabling you to count blank cells in a given range. The syntax is =COUNTBLANK(range). For example, to check how many cells in the range A2 are empty, you should use the formula =COUNTBLANK(A2:A20).

COUNTBLANK EXAMPLE IN EXCEL

Now you know the basics of these five powerful count formulas in Excel. With COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK on your side, you can easily analyze, filter, and summarize your data based on specific conditions, presence of data, and blank cells. 

Good luck!

Leave a Comment