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 accounts payable data (vendor name, invoice amount, due date), and you want to count the number of invoices with amounts greater than $1,000. 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 the syntax is 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 E where the amount due is greater than a specific number (let’s say 1,000, as in the example earlier), you can do it by applying the below function:
=COUNTIF (E:E, ">1000")
This will return the number of cells where the number is greater than 1,000. 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.
For the sake of the following examples, we’ve loaded a dataset that contains accounts payable data from QuickBooks using Coupler.io. The schema of the dataset includes:
- Column A: Vendor Name
- Column B: Bill Number
- Column C: Bill Date
- Column D: Due Date
- Column E: Bill Amount
- Column F: Bill Updated Date
- Column G: Status
Coupler.io is a data and reporting automation solution that allows you to automate exports from multiple cloud sources to Microsoft Excel. You can load data from financial apps like QuickBooks or Xero, advertising platforms, CRM tools, databases, and other 60 sources. Your data stays fresh with automatic scheduled updates, eliminating manual data entry and potential errors from copy-pasting. Try your data integration with Excel right away for free! Select the desired source app in the form below and follow the in-app instructions to load data to Excel.
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 bills that have amounts greater than zero. We can do this by applying the below formula:
=COUNTIF(E:E,">0")
This formula will go over the “Bill Amount” column (E) and will count any instance where the amount is above zero. As accounts payable managers often need to track how many bills require payment, this simple formula quickly shows the total number of bills with amounts greater than zero.
In our accounts payable dataset, this returned the number of 123 bills with amounts greater than zero.
Excel COUNTIF greater than or equal to 0
But how can we include bills where the amount is exactly zero? With a minor tweak to the formula, we can include and count these bills as well:
=COUNTIF(E:E,">=0")
Applying this formula, we can see that there were a few additional instances with bill amounts of exactly zero (perhaps placeholder entries or fully credited bills), bringing our total to 130 bills (compared to 123 from the previous example). This helps accounts payable teams track all entries in their system, including those with zero amounts.
And applying a single filter we can see these exact instances:
Excel COUNTIF length greater than
Other than numbers, you can also apply Excel COUNTIF to check the number of characters in a specific cell (length of the string). In order to do that, we need to use a combination of other functions since COUNTIF cannot directly evaluate string lengths across a range. Here’s how we can count vendor names that are longer than 20 characters:
=SUMPRODUCT(--(LEN(A2:A1000)>20))
This formula uses SUMPRODUCT with LEN to count vendor names in column A that are longer than 20 characters. In our dataset, this returns 9 vendors.
Alternatively, Excel COUNTIF can count the number of values with an exact number of characters in text length. The question mark is used to match any character, and you need to use it as many times as the number of characters you target. For example, let’s find out how many vendors have the name of exactly 15 characters. Here is the formula:
=COUNTIF(A:A,"???????????????")
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(D:D,">"&TODAY())
This formula counts the number of bills in our dataset where the due date (column D) is after today’s date (upcoming bills yet to be paid). This helps financial teams track upcoming payments and manage cash flow proactively.
You can also use a specific date as a criterion using the format you prefer, for example,
=COUNTIF(D:D,">3/18/2025")
or
=COUNTIF(D:D,">2025-03-18")
This would count bills with due dates after March 18, 2025. In our dataset, this returns 16 bills due after this date.
Financial teams using Coupler.io to automate QuickBooks data imports can take this date analysis further. By scheduling automatic daily refreshes, AP managers can run these COUNTIF date formulas each morning to identify which invoices need attention without any manual export work. This proactive approach helps prevent late payments and capture early payment discounts.
Excel COUNTIF one column is greater than another
Last but not least, another common criterion is comparing values between columns. While COUNTIF cannot directly compare two ranges, we can use SUMPRODUCT for this purpose. Let’s compare bill dates (column C) with due dates (column D) to find bills with short payment terms:
=SUMPRODUCT(--(D2:D1000-C2:C1000>30))
This counts bills where the payment term (difference between due date and bill date) is greater than 30 days. In our dataset, this formula identifies 43 bills with short payment terms.
Excel COUNTIF multiple criteria
COUNTIF in Excel does not allows you to count values based on multiple criteria. However, you can use SUMPRODUCT for this.
For example, if we want to count how many bills have amounts (column E) that are greater than $1,000 and also have due dates (column D) after April 1, 2025:
=SUMPRODUCT((E2:E1000>1000)*(D2:D1000>DATEVALUE("4/1/2025")))
This formula counts instances where both conditions are true: the bill amount is greater than $1,000 and the due date is after April 1, 2025. In our dataset, this returns 2 bills meeting both criteria.
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.
For accounting and financial teams handling large datasets, automating these calculations is crucial. Using Coupler.io, you can set up automatic imports from QuickBooks or other accounting systems directly to Excel or Google Sheets, ensuring your COUNTIF formulas always work with up-to-date data. This saves hours of manual data work and provides real-time insights into your accounts payable status.
So, don’t wait! Export your data, write up your functions and start extracting all the valuable insights!
Automate data flows to Excel with Coupler.io
Get started for free
