Excel COUNT IF: Simplifying our data counting

Key information:

The function COUNT YES allows us count elements of a selection of data (“range”) based on a condition (“criterion”) defined by us. Excel will evaluate the condition in each of the cells that we indicate and will see how many meet it. Then it will give us the total number of those that complied.

The basics:

• Purpose: allows us to count cells in a range of data, according to a criterion defined by us. 
• Characteristics: It is the union of two other Excel functions, COUNT and IF, in the same package. It can be used in any direction.
• Syntax: =COUNTIF(range; criterion)
• Arguments:
  • range = The selection of cells to be counted in
  • criterion = The condition according to which it will be counted

Step 1: Selecting a data range

When using the Excel COUNT IF function we have to select the data about which we want to count something. We call this “range” and it can be selected in any direction. In the following example we want to count the Xs inside the square:

Since the table goes from B3 to E8, we select this array as our range:

Thus the range is defined as: 

• range B3:E8

As we see in the example, the function correctly showed us that in our range there are 8 cells that contain the letter X:

Make sure you select the cell range correctly to avoid errors.

Step 2: Defining a criterion

When applying the Excel COUNT YES function we must define a criterion, that is, the condition on which Excel will count our cells. This condition can take different forms depending on what we want to look for.

COUNT IF according to a numerical criterion:

We can use the function so that count the cells that contain the numerical criteria that we give you. Taking the example of the following image, We want to see how many people are 35 years old:

Since we are looking at the years, our range becomes the “Age” column, which goes from cell D4 to D8. In this case the condition is to be 35 years old, so the function includes a 35 as a criterion. So:

• range = D4:D8 (Data column “Age”)
• criterion = 35 (The person is 35 years old)

That is, our COUNTIF function corresponds to:

=COUNTIF (D4:D8,35)

This results in 2, that is, the number of cells that meet the condition:

In turn, the condition of the Excel function COUNT IF can be the value of another cell. In the image we see an example of how to enter this:

Now the condition is given by cell H4, which contains the number 35, so we will obtain the same result.

COUNT IF according to a criterion in words:

We can also use the Excel COUNT IF function to count according to a criterion in words. Now when writing the condition we must worry that the word is enclosed in quotes (“”), since with this we tell the COUNT IF function that it is a text. In the following example we want to count all the people named Juan:

We can see that now our range is the “Name” column. The result that COUNT IF will give us is 2.

As with numbers, it is possible to remove the condition from another cell, where it is not necessary to add quotes because the COUNTIF function will recognize that the original cell is text:

The condition in this case comes from cell H3, which says Juan, so COUNT IF will count the number of times the name “Juan” is repeated in our range, and will give us a result of 2.

Logical operators

We can combine the Excel function COUNT IF with logical operators (>,<,<>,=) to make more complex numerical conditions. These operators are:

• > Greater than
• < Less than  
• <> Different from
• = Same
• >= Greater than or equal to
• <= Less than or equal to

Now we want the COUNT IF function to show us all those people who are over 30 years old. For this we use > (greater than) and 30 as parts of our condition. But since > corresponds to text, our entire condition must be enclosed in quotes:

This function will give us a 4, since everyone except Juan Bilbao is over 30.

Suppose that now we want to count all those people who do not have the position of analyst. We use the COUNT IF function including the operator <> (not equal to) and the word analyst.

Our rank now is the charge column. This gives us the following result:

Ninja Tip: The COUNT IF function does not recognize capital letters, so using “Analyst”, “analyst” or “AnAlIStA” as a condition gives the same result.

We can also use logical operators and conditions that come from a cell, but using the “&” character. In this case, it is the logical operator that is enclosed in quotes, but not &. The following image shows the example we used previously about people over 30 years old:

Ninja Tip: You can use this function by including other functions as conditions to achieve better results. An example is to use the Excel function AVERAGE and “>” to count those people who are above the average age.

Ninja Tip: COUNTBLANK is a variation of COUNTIF that counts the number of blank cells in a range, which can be useful when working with a lot of data to know how many are missing information.

Wild cards

There are certain characters that can be used in the COUNT IF function, known as “wildcards,” that allow special actions to be performed on the conditions.

Asterisk:

Depending on where the asterisk is located in the condition (*), it can tell the Excel COUNT IF function different things:

• If it comes before the condition (example: “*dro”) it indicates that it may come something before what is written. In the example, the COUNT IF function will recognize “Pedro” and “Alejandro” as cells that meet the condition.
• If it comes after the condition it indicates that it can come somewhat after what is written. If we now write the condition as “Pe*”, COUNTIF will recognize “Pedro”, but not “Alejandro”.
• It can also come before and after the condition, indicating that something can come before and after what is written. If we write the condition as “*dro*”, COUNTIF will recognize “Pedro Pablo” as a cell that meets the condition.

In the following example we want to know how many names start with the letter “M”, so we write “M*” as a condition, since this way we tell Excel that the condition is that it starts with M, but that anything can come later :

This will give us a result of 2:

Question mark:

The question mark (?) tells the function that any other character can go in the place where we place the sign. In the following example we want to see how many people are named Juan or Joan, who only differ in the second character, which we replace with “?”, to tell Excel that any character is allowed in that place:

This will give us a result of 2, since Excel recognizes the names Juan and Joan as meeting the condition:

We must remember that the “?” replaces only one character, so in the previous example COUNT IF does not include Julian. If we wanted to count how many Juliáns there are, the condition would be “J???án”.

COUNT IF SET: Counting according to several criteria

• Purpose: COUNT IF SET is similar to COUNT IF, but allows you to include different conditions to obtain a single result.
• Characteristics: Only the first range and criteria are required, as you can have up to 126 extra range/criteria pairs. Extra ranks cannot exceed the size of the first rank.
• Syntax: =COUNTIF.SET(criteria_range1; criterion1, [criteria_range2; criterion2]; …)
• Arguments:
  • criteria_range1  = Range where criterion 1 is evaluated
  • criterion1 = Condition upon which it is counted in rank 1
  • criteria_range2, criterion2… = Extra pairs of ranges and criteria

The Excel function COUNT IF SET allows us to count data based on several criteria. In the following example we want to see how many people are over 30 years old and are analysts. For this, our first condition will be that the person is over 30 years old and their rank will be the “Age” column. Our second condition will be that the person is an analyst and their rank will be the “Position” column:

The elements that our COUNT IF SET function must contain are:

• criteria_range1  = D4:D8 (Column “Age”)
• criterion1 =  “>30” (Being over 30)
• criteria_range2  = E4:E8 (Column “Position”)
• criterion2  = Analyst (Be an analyst)

That is, our function corresponds to:

=COUNTIF SET(D4:D8,”>30”, E4:E8, “Analyst”)

The result we obtain is 1, since only María Tapia meets both conditions:

Nested If and And: Learning to combine both functions

Combining the Excel IF and AND functions allows us to check different conditions, so that we then obtain a predetermined result depending on whether these conditions are met or not. This is useful for analyzing different types of information, but summarizing the result in the same cell.

You can complement your learning with the following video:

The basics

To combine functions YEAH and AND, it is necessary to understand each of the two:

IF function:

Purpose: The IF function allows us to evaluate whether a condition is true or false. If true, it performs a given action, otherwise it performs another action. 

Characteristics: Allows you to evaluate with different symbols, for example, equal, less, greater than or equal, unequal, etc. Numeric or text values can also be evaluated in the logic test.

Syntax: =IF(logical_test; [value_if_true]; [value_if_false]

Arguments:

logic_test = Required. Value or expression that we seek to evaluate.

value_if_true = Optional. Value delivered when the evaluation is true. This value can be text, calculations, a reference to another cell, or other functions.

value_if_false = Optional. Value delivered when the evaluation is false. This argument is optional, if not set, Excel will respond “FALSE” automatically.

For more information and examples of how to use the IF function you can go to the following article.

AND function

Purpose: The function AND It allows us to know if all the conditions of a logical proof are true.

Characteristics: If all the arguments are true, it returns “TRUE” as a result (logical value). In any other case, it gives us “FALSE” as a result.

Syntax:

=Y(logical_value1;[logical_value2];…

Arguments:

logical_value1 = Required. First of the logical values to evaluate. It can be a condition, the important thing is that the result has to be a logical value (TRUE OR FALSE)

logical_value2; … = Optional. The rest of the logical values to evaluate. It can be a condition, the important thing is that the result has to be a logical value (TRUE or FALSE)

Understanding the IF function

The IF function allows us to evaluate conditions in a cell or selection of cells. These conditions can be with arguments that are in text or numeric format. The only thing to remember is that if we are dealing with an argument in text format we must write it between quotes (“”). Also very common are logical operators (>,<,=,etc).

In the following example we want to see if people have a salary greater than $100:

How we can see the arguments of the IF function are as follows:

logic_test = C3>$100

value_if_true = Yes

value_if_false = No

So the function will evaluate the argument found in the “Salary” column and see if it corresponds to a value greater than $100. If it is, the function will understand the logical test as “TRUE”, in which case it will return the word “Yes”. If it is not, it will understand it as a “FALSE” and will deliver the word “No”. In this way the results are the following:

Let's see what happens if we want to include text in the condition as well. Let's assume that now in the example we want to give a bonus to people with a salary less than or equal to $100:

Now the condition is that the column “Salary greater than $100? be equal to “No”. When it is met, the word “Bonus” appears in the “Complement” column, while when it is not met this cell remains blank:

Understanding the Y function

Excel's AND function will help us know if different cells or conditions are all true (in logical value). To understand this, let's look at the following example, where we want to know if all the people are over 18 years old:

In this case, in the column “Are you over 18 years old? We only leave a logical test, which corresponds to seeing if the cell to its left corresponds to a number greater than 18. In this way, the result can only be “TRUE” or “FALSE” which correspond to logical values:

Now we use Excel AND function to see if all logical values are true:

And since all the included cells are true, the result is also true:

However, if we now make one of the ages not meet the condition, the general result becomes false:

Ninja Tip: The AND function omits those cells whose result is not a logical value, so we must be careful not to lead to general conclusions that are wrong. For example, if instead of saying “FALSE” when the condition was not met you said “No”:

Here “Dusan Radic” does not meet the condition, but the result is not a logical value, so the AND function does not recognize it and indicates that everyone is over 18 years old.

Alternatively, we can do the different logical tests directly in the Y function, which will obtain the same result:

Combining Excel IF and AND functions

Having understood each of the Excel IF and AND functions, combining them is very simple. What we will do is use the AND function to evaluate different conditions, so that later with that result the SI function determines what we want it to deliver to us in the cell itself.

In the following example we want to give a bonus to those people who are over 30 years old and earn less than $100:

In this case the arguments of the IF function are:

logic_test = Y(C3>30;D3<10)

value_if_true = Yes

value_if_false = No

As we can see, the logical test is to meet both conditions (using the AND function), that the cell in the “Age” column is greater than 30 and that the “Salary” column is less than 100. Only in that case the IF function tells us will deliver the text “Yes”:

In the example, only Clemente Baraona meets both conditions, making him the only person who has a bonus.

This method of combining functions also works if the conditions refer to the same cell. For example, now we only want there to be a bonus if the salary is less than $150, but greater than $60:

With which we obtain the following result:

Ninja Tip: The rest of the arguments of the IF function can also be function operations. For example, we may want the SI function to give us the 10% of the salary when there is a bonus, so we only multiply the cell in the “Salary” column by the 10%:

Ninja Tip: Everything previously learned can also be used by replacing the AND function with the function EITHER to check if any of all the conditions that we give to the IF function are true.​

Frequent questions