Excel COUNTIF Greater Than – Formula Syntax & Example
For over 35 years, Microsoft Excel has been one of the most widely used spreadsheet software helping millions of people and their businesses. It provides a wide range of pre-defined functions to handle data and COUNTIF is the most common one for counting cells matching specific conditions.
In this article, we will review how COUNTIF works with a specific comparison condition while going over specific use cases.
Excel COUNTIF greater than and less than
The Excel COUNTIF function can work great to count data where a specific comparison condition is fulfilled. You can use the COUNTIF to count cells with values greater than and less than a number you specify.
For example, let’s say you have a table with employee data (name, department, and salary), and you want to count the number of employees making more than 5k per month. The function COUNTIF greater than is a great way to find the result you’re looking for.
In the following section, we’re going to see what’s the syntax for this function and how you can use it with specific examples as mentioned above.
Excel COUNTIF greater than syntax
The COUNTIF function requires two main inputs:
- range: which is the specific set of cells that you want to go over
- criterion: which is the condition you want to be fulfilled
The exact syntax is shown below:
=COUNTIF (range, criterion) Excel COUNTIF greater than criteria
But “how can I apply the ‘greater than’ criterion” you may ask? Well, that’s easier than it sounds.
If you want to count the instances in column A where the number is greater than a specific number (let’s say 5000 as in the example earlier), you can do it by applying the below function:
=COUNTIF (A:A, ">5000")
This will return the number of cells where the number is greater than 5000. It’s that easy!
How to use Excel COUNTIF greater than
Enough with the theory now, let’s go over some specific examples to see how Excel COUNTIF greater than works in action.
To do that, we must first load our dataset in Microsoft Excel. We can either manually export our data from the database in CSV format and then load it in Excel or we can use Coupler.io and its integration with Excel to import everything with only a couple of clicks.
Coupler.io is a data integration solution to automate exports of data from multiple sources to Microsoft Excel, Google Sheets, or Google BigQuery.
For the sake of the following examples, we’ve loaded a dataset that contains the daily average temperature (Fahrenheit) for a set of cities. The schema of the dataset is shown below:
Excel COUNTIF greater than 0
Let’s see how we can apply the Excel COUNTIF greater than zero criterion in order to find the number of days that any city had a positive average temperature. We can do that by applying the below formula:
=COUNTIF(F:F,">0")
This formula will go over the F column and will count any instance where the temperature is above zero. As the dataset is broken down into days, the final number shows the total number of days where the temperature was above zero in any of the cities.
Excel COUNTIF greater than or equal to 0
But how can we include the days when the temperature was exactly zero? With a minor tweak to the formula, we can include and count these days as well:
=COUNTIF(F:F,">=0")
Applying this formula, we can see that there were only three instances with an average temperature of exactly zero (88588 instances instead of 88585 from the previous example):
And applying a single filter we can see these exact instances:
Excel COUNTIF length greater than
Other than numbers, Excel COUNTIF can also be applied to check the number of characters in a specific cell (length of the string). In order to do that, we can use it as shown below:
=COUNTIF(O:O,"???????")
Applying Excel COUNTIF, we are counting the number of instances where the text length is exactly 7 characters. The question mark is used to match any character and it’s repeated 7 times. The end result would look like the below (only 22 countries in the dataset are using exactly 7 characters):
You can tweak this Excel COUNTIF formula to count the number of values where the text length is greater than a specified number of characters.
Excel COUNTIF date greater than
Another great use case for the Excel COUNTIF formula is to count a column based on a date condition (e.g. when the date is greater than a given date). Let’s see an example of how that works:
=COUNTIF(G:G,">1/1/2019")
The final result is 52113 which is the number of records in our dataset which report a temperature after January 1, 2019.
Excel COUNTIF one column is greater than another
Last but not least, another common criterion is Excel COUNTIF greater than another cell. It means that you want to count the instances in one column based on if the value is greater than another column.
=COUNTIF(F:F,">"&H2)
This formula counts any instance where the average temperature for a given date is greater than the total average temperature of all dates. The result looks like the below:
COUNTIF in Excel and Google Sheets as well?
The COUNTIF formula is one of the most widely used when it comes to counting records based on a specific instance. Besides Microsoft Excel, you can use COUNTIF in Google Sheets with exactly the same syntax.
So, don’t wait! Export your data, write up your functions and start extracting all the valuable insights!
