How to Use COUNTIF and COUNTIFS Function in Excel

In Excel, COUNTIF and COUNTIFS are two powerful functions that can allow you to sit back and relax while Excel does all the tedious counting for you. Read on to learn how to utilize these functions.

What Is COUNTIF in Excel?

COUNTIF is a core function in Excel that counts the cells that meet a certain condition. The syntax for this function includes a range of target cells, followed by a single condition. The COUNTIF function supports both logical operators and wildcards. With these two, you can further expand or narrow down your condition.

        COUNTIF(range, criteria)

The COUNTIFS function, as the name implies, serves a similar purpose as the COUNTIF function. The main advantage of the COUNTIFS function is that it can supply more than one condition for multiple cells ranges. The COUNTIFS function supports logical operators and wildcards as well.

        COUNTIFS(range1, criteria1, range2, criteria2)

Supported logical operators include:

< Smaller than
> Bigger than
=< Smaller than or equal to
>= Bigger than or equal to
= Equal to

Supported wildcards include:

* Any number of any character.
? Single number of any character.
~ Turns the wildcard immediately after the tilde into a normal character.

How to Use COUNTIF in Excel

To use the COUTNIF function in Excel, you need two define two things for it. First, the target range where you want the formula to count cells, and second, the criteria upon which the formula will count the cells for you. Let's see these in action with a simple example.

In this example, we have a list of apples and their colors. The goal is to write a formula that will count the number of red apples and then display it in a cell. Using a cell range where apple colors are placed as the range, we will instruct the formula to count only the red ones in that range when using the COUNTIF function.

Select the cell where you want to display the result of the count. In this example, we're going to use cell E3.
Once you have the cell selected, go to the formula bar and enter the formula below:
```
        =COUNTIF(A3:A11, "Red")
    
```
Since the target range in this example, where the apple colors are located, is cells A3 to A11, the formula is given A3:A11 in the first part. The second part, which is the criteria, has to specify that the COUNTIF function counts only the red apples. Thence the “Red” in the second part. Remember that you have to put text criteria between quote marks.
Press Enter.
Excel will now count the number of red apples and display it.

The COUNTIF function allows you to accomplish many wonderful things, especially when used with wildcards and other functions, but it can only consider one condition. In contrast, its relative, the COUNTIFS function, operates on multiple conditions for multiple ranges.

How to Use COUNTIFS in Excel

The COUNTIFS function is basically a more sophisticated version of the COUNTIF function. The main advantage that COUTNIFS holds over COUNTIF is that it supports multiple conditions and ranges.

However, you can also define a single range and a single condition for the COUNTIFS function, making it no practically different from the COUNTIF function.

One important thing that you should understand about the COUNTIFS function before using it is that the COUNTIFS function does not simply sum the results of cells that meet the criteria for each cell range.

In reality, if you have two conditions for two ranges, the cells in the first range are filtered twice: once through the first condition, and then through the second condition. This means that the COUTNIFS function will only return values that meet both conditions, in their given ranges.

You can get a better understanding of what exactly the COUNTIFS function does by studying the example below.

We've extended the example from the previous section, and now aside from the color of the apples, there's a column describing their size as well. The ultimate goal in this example is to count the number of big red apples.

Select the cell where you want to display the results. (In this example, we're going to display the count of big red apples in cell E3.)
Go to the formula bar and enter the formula below:
```
        =COUNTIFS(A3:A11, "Red", B3:B11, "Big")
    
```
With this, the formula tests the cells from A3 to A11 for the condition “Red”. The cells, which pass the test, are then further tested in range B3 to B11 for the condition “Big”.
Press Enter.
Excel will now count the number of big red apples.

Observe how the formula counts cells that have both the red and the big attribute. The formula takes the cells from A3 to A11 and tests them for the color red. Cells that pass this test, are then again tested for the second condition in the second range, which in this case, is being big.

In conclusion, the ranges and conditions after the first range and condition further narrow down the count filter and are not independent of each other. So then the final result of the formula is apples that are red and big. You can count the number of red or big apples by combining the COUNTIF function with the SUM function.

Select the cell where you want to display the result of your formula. (In this example, we're going to use cell E6.)
Enter the formula below:
```
        =SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))
    
```
This formula will count the cells containing red apples, then the number of cells containing big apples, and finally, it will sum the two numbers.
Press Enter.
Excel will now count and display the number of big or red apples.

Count More With Excel

If you have multiplied conditions for a count, or simply too long a list, then counting manually can become very frustrating. With Excel and its COUNTIF and COUNTIFS functions, you can write a formula that will take care of the counting process for you.

The small effort you make to learn this new Excel formula will allow you to gain great benefits in the future. Perhaps combining SUMPRODUCT and COUNTIF will yield some good?