Microsoft Excel has changed the lives of many people, with its numerous data-driven functions, conditional logic, and flexible array of formulas. Whenever you have to work on any data requirements, Excel continues to be the first preferred choice for many users.

Since data are quantifiable entities, Excel's range of count functions is quite handy, especially when dealing with large datasets. Here are some of the most pertinent Excel count functions you should know since they can help you deal with your data assets effortlessly.

What Are the Count Functions in Excel?

Like some of the other standard functions in Excel, the count functions are ubiquitous, which anyone and everyone can use. From counting non-blank values to counting blanks within a range, there is an equivalent count function to perform all your tasks.

Here’s a peek at some of the various count formulas and their uses:

1. The COUNT Function

The COUNT function is the most basic function within Excel, and its usage is restricted to counting numbers in a pre-defined range. It's quite easy to use the COUNT formula in Excel, and you can ace it quickly.

Syntax of the COUNT Formula

         =count(value1, value2, ... value_n)
    

Let’s demonstrate the use of the count function with an example. Consider the following data consisting of some random numbers. You can use the COUNT formula to calculate the total number of entries.

You can use the following formula to count the total entries in column A.

        =count("A1:A9")
    

The output is 8, which shows the total count of entries in the respective column. Here's the output:

Remember, this won’t count any text values, so if you have text values within the range, the count function will only return the count of all the numeric values.

2. The COUNTA Function

The COUNTA function in Excel counts the number of non-blank cells in a range of numbers. If you have any blank values within a range of values, the COUNTA formula will exclude all those cells and return a count of the populated cells.

Syntax of the COUNTA Function

         =counta(value1, value2, ... value_n)
    

Using the above data range with a few tweaks, let's see how you can use the COUNTA function:

         =counta("A2:A9")
    

In this example, the COUNTA function returns the output as 6, i.e., the total number of populated cells. It negates the values of the blank cells.

3. The COUNTBLANK Function

The COUNTBLANK function works oppositely. For example, wherein the COUNTA function counts the total number of populated cells, the COUNTBLANK function counts the number of blank cells in a defined range.

Syntax of the COUNTBLANK Function

         =countblank(value1, value2, ... value_n)
    

Let's work with the following example:

To count the number of blank values, use the following formula:

         =COUNTBLANK(A2:A9)
    

The formula returns a value of 2 since only two blank values exist in the defined range.

4. COUNTIF Function

Excel's COUNTIF function is quite interesting, as it combines the count function with a conditional function, IF. One of the noteworthy features of the COUNTIF function is its versatility, considering it works equally well with text and numeric functions.

The syntax listed below works for both value types: text and numeric.

Syntax of the COUNTIF Function

         =countif(range, criteria)
    

How to Use the COUNTIF Function With Numbers

When you are working with numeric data, you can use any of the count functions. But if you want to count the total occurrences of a specific number, you need to use the COUNTIF function specifically.

For example, in the data below, suppose you want to find out how many times the number 2 occurs in the data range. Here's how you will do it:

         =COUNTIF(A2:A9, 2)
    

As shown in the syntax, you must define the range, followed by the criteria. Since you want to count the number of times 2 occurs in the data range, the output is as follows:

How to Use the COUNTIF Function With Text Values

In Excel, count cells with specific text using the COUNTIF function. In such a scenario, if you have a dataset with all text values or a mixed range of values, i.e., text and numbers, you can use the COUNTIF formula to count a specific value effectively.

Let's find out how often the name Andy occurs within this range using the COUNTIF function.

        =countif(A2:A9, "Andy")
    

Pass text lookup criteria within quotes; remember, the text lookup value isn't case-sensitive in Excel.

The output is 2, as there are only two results matching these criteria:

5. The COUNTIFS Function

Like the COUNTIFS function, the COUNTIFS function combines the count function with multiple conditions. Using a combination of lookup values, you can use this functional formula to calculate the count.

It's essential to note that the COUNTIF and COUNTIFS functions are different; even though they might seem related, there is a subtle difference in how you use both count formulas. The syntax listed below works for both value types: text and numeric.

Syntax of the COUNTIFS Function

         =countifs(criteria_range1, criteria1, criteria_range2, criteria2, ...)
    

Learn to Use the COUNTIFS Function With Numeric Values

The COUNTIFS function syntax is quite similar to its counterpart. In the below example, since you have two columns of data, you can pass multiple conditions and display a result accordingly:

If you want to display a count of numbers that are greater than 6 and less than 5, here's how you will do it:

        =COUNTIFS(A2:A9, ">6", B2:B9, "<5")
    

The output returns 1, which is shown below:

Since there is only one row where the number in columns 1 and 2 meets the criteria, the formula displays the output 1.

How to Count Text Values With the COUNTIFS Function

One of the biggest questions users often have is, "How to count cells with text in Excel". The COUNTIFS formula is quite flexible, and you can use it to work with multiple text lookup values.

For example, suppose you want to calculate how often A has scored more than 50 marks in a particular subject.

You can calculate the count using the COUNTIFS function, as follows:

        =COUNTIFS(A2:A9, "A", B2:B9, ">50")
    

The output returns the value 2 since only two entries match the specified criteria.

Empowering Your Data With Excel’s Functions

Excel's list of functions continues to impress end-users. From calculating superfluous counts to using logical functions to perform elaborate analysis, there is a function for all data requirements. If you can master these functions, you can carry out any form of advanced analysis, in a few steps.