How to Use the Countif Greater Than Function in Excel

MS Excel, as we all know, is a spreadsheet application prepared by Microsoft for various operating systems like Windows, iOS, and also for Mac. It is known to carry out calculations by implementing different kinds of inbuilt formulas and functions. One such function in Excel is COUNTIF, which is primarily used for counting cells that have unique values in a range that meets certain criteria or conditions. This particular function can be implemented for all kinds of data including numbers, text, dates, or even for those cells which are blank. Apart from touching upon different aspects of the COUNTIF function, our main focus today will be on the logic of the COUNTIF greater than function.

countif greater than -magoosh

COUNTIF is an in-built function and is recognized as the function through which statistical figures can be prepared. You can do several calculations through COUNTIF to find out exactly how many cells in the worksheet have a number which is greater than, or even less than the number you have specified.

What Is the Syntax of COUNTIF?

COUNTIF (range, criteria)
This is the syntax of COUNTIF function.
There are just two aspects, range and criteria, which are required to find the exact result.

Let’s briefly take a look at both these aspects.

  1. Range: When one or more than one cell is taken into account for counting purposes, then this is regarded as a range. Any user can insert the appropriate range into a formula for which the user seeks to get a result. In Excel, an example of range would be – A1: A15
  2. Criteria: Specific, particular conditions that you specify to let the function know which cells you want taken into account for counting purposes. It could be any string, number, reference to any particular cell, or a specific expression. For example, in Excel, particular criteria would be used in the following sequence:
    “30”, A5, “>=30”, “some text”

The COUNTIF function is described better with the help of an example. Suppose we need to find out the exact number of times Roger Federer won Wimbledon titles from the year 2003 to 2015, as compared to other players like Andy Murray, Rafael Nadal, Novak Djokovic, and few others.

We show this through a diagrammatic representation.

From the above list we can see that from the year 2003 till 2015, there have been quite a number of occasions when Roger Federer won the title. We also see that players like Rafael Nadal, Andy Murray, and Novak Djokovic also won the coveted and most prestigious trophy in the world of tennis—usually more than once.

So when we have to find the exact number of times Federer won the title within that 13-year period, we would use the COUNTIF formula.
The formula that you should insert in the formula bar is:

fx=COUNTIF (B2:B13, “Roger Federer”)

This formula gives us the exact number—and now we know that the tennis sensation had the honor of getting that trophy seven times.

excel-countif-greater-than-function-trophy-magoosh

The above illustration shows how the COUNTIF function gets those cells, which are filled with text, counted when we need to find a particular figure.

Before explaining exactly how the COUNTIF greater than function is used in Excel, let’s take a look at how COUNTIF is used in a formula when we need to secure a result for text and numbers.

COUNTIF Formula – Text and Numbers with Exact Match

Now we will see how we can still get the same result as in the prior example, but through the use of COUNTIF formula for text. We will do this without even using text, but by only referring to the cell that contains that specific word or text.
For example:
=COUNTIF (B3:B12, B6)

In the same manner, we can apply the COUNTIF formula for numbers:

Now, say that we have to find out how many quantities are equal to 10. The formula for this would be:

=COUNTIF (B2: B9, “=10”)

So, it counts out the cells and gives us the result as 1. We can clearly see that there is only one fruit, oranges, which has values of 10.

COUNTIF Greater Than Function

For counting cells with values greater than the specified number, we just need to add a corresponding operator to the criteria. This is illustrated in the table below. In that table you can also find exactly what the formulas would be if we want to count cells with values less than, or equal to the specified number:

Through the above table, it is clear that through COUNTIF we can execute formulas for counting cells having:

  • values greater than the specified number,
  • values less than the specified number, and
  • values equal to the specified number.

Here, we will only focus on the COUNTIF greater than function and move ahead with it for better understanding.

Count Cells Based on the Value of Another Cell

The COUNTIF greater than function is also used to count those cells which are based on the value of another cell. In that case, you only need replace the number in the criteria with a cell reference.

It should also be taken into consideration that, in case of a cell reference, you need to enclose the operator in quotes and include an ampersand before the cell reference.

We can understand this through an example. Taking the previous example into consideration, suppose we need to calculate the cell in the region B2: B9, with values greater than a value in cell B5. We would write the following formula in the formula bar:

=COUNTIF (B2: B9, “>” &B5)

The table is given below:

After putting the COUNTIF greater than function in the formula—=COUNTIF (B2:B9, “>” &B5)—we get the result as 2 in cell C2. The value in B5 was 14 and in the range B2 to B9. And only two amounts were greater than 14—that is, 18 and 17 for Lemons and Guavas respectively.

So now we have seen how COUNTIF greater than function is used in Excel to quickly find the values which are greater than the one specified. Be sure to check out our other blogs and videos in this Excel series, and you’re sure to become a pro!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!