COUNTIFS Greater Than in Excel: Criteria and Examples

Formulas & functions · Updated November 22, 2026

To count values greater than a number in Excel, use COUNTIFS with the greater-than operator inside a quoted criteria string: =COUNTIFS(B2:B10,">100"). Replace 100 with your threshold, or join the operator to a cell reference when the threshold may change.

What COUNTIFS Does

COUNTIFS counts rows or cells that meet one or more conditions. It is useful when “greater than” is only part of the test—for example, counting sales above $100 only in a particular region. Its criteria are applied together: a record is counted only when every specified condition is true. In that sense, COUNTIFS uses AND logic across its criteria pairs.

The function has been available in Excel since Excel 2007 and is included in Microsoft 365 for Windows. It is also available in Excel for Mac and Excel for the web. The function works the same way across those versions, although regional settings may require semicolons instead of commas as formula separators. The examples here use commas.

COUNTIFS is not limited to numeric values. It can test dates, text, and other conditions too. For a straightforward count based on just one condition, COUNTIF is another option; COUNTIFS is often convenient because you can add more conditions later without changing functions.

How the Greater-Than Criteria Works

Excel treats a comparison operator in a COUNTIFS criterion as part of a text criterion. Put the operator and a fixed number together inside quotation marks. The quotation marks are required; without them, Excel cannot interpret the comparison as intended.

Suppose cells B2:B10 contain these values: 125, 90, 100, 160, 101, 80, 250, 100, and 99. The following formula counts the values strictly greater than 100. It returns 4: 125, 160, 101, and 250. The two values equal to 100 are not included.

With the numbers in B2:B10, this formula counts values above 100:

=COUNTIFS(B2:B10,">100")

Greater than is different from greater than or equal to. Use the two-character operator >= when values equal to the threshold should count. For the same sample, there are 6 values greater than or equal to 100: 125, 100, 160, 101, 250, and 100.

Use a Cell as the Threshold

A fixed threshold is convenient for a one-off calculation, but a cell reference makes the count easier to update. When a criterion combines an operator with a cell value, join them with the ampersand (&). The operator stays in quotation marks; the cell reference does not.

With sales values in B2:B10 and the threshold 100 in E1, this formula returns 4. Changing E1 to 150 recalculates the count without editing the formula; for the sample values above, the result then becomes 2, for 160 and 250.

With values in B2:B10 and the comparison threshold in E1, use:

=COUNTIFS(B2:B10,">"&E1)

This construction also works for >=, <, <=, and <> (not equal to). For example, to count values at least as large as the threshold, place >= inside the quoted text and concatenate the reference. Keep the operator directly next to the ampersand and cell reference so Excel reads it as a single criterion.

Combine Greater Than with Other Conditions

COUNTIFS can test several conditions at once. Each condition has a criteria range and a criterion. The ranges must correspond to the same rows: the value on a given row is checked against the other conditions on that row. This makes it possible to answer questions such as “How many West-region sales exceeded 100?”

For example, suppose A2:A7 contains West, East, West, West, East, West, and B2:B7 contains 120, 150, 95, 180, 110, 101. Three West records have sales greater than 100: 120, 180, and 101. The formula below returns 3.

With regions in A2:A7 and sales in B2:B7, count West-region sales above 100:

=COUNTIFS(A2:A7,"West",B2:B7,">100")

You can add further pairs for conditions such as a product name, status, or date range. All of them must be true for a row to count. A common misunderstanding is that separate criteria pairs automatically mean “this condition or that condition.” They do not: they work as AND conditions. If you need OR logic, you may need to add separate counts or use another approach, taking care not to count the same row twice.

Greater-Than Tests for Dates and Text

Excel stores real dates as serial numbers, so greater-than comparisons can identify dates after a cutoff. To count dates in a particular year, a robust pattern uses an inclusive start date and an exclusive start date for the following year. That avoids problems with times attached to date values and does not depend on how a date is displayed in the worksheet.

Assume A2:A5 contains January 5, 2026; December 31, 2026; January 1, 2027; and April 20, 2026. The formula returns 3 because three dates fall on or after January 1, 2026, and before January 1, 2027.

With dates in A2:A5, count records in calendar year 2026:

=COUNTIFS(A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2027,1,1))

Text comparisons can also use operators, but they may not behave like numerical comparisons. If your goal is to find text that begins with certain letters, use a text criterion with a wildcard rather than assuming words have a useful numeric order. COUNTIFS supports the asterisk wildcard for any sequence of characters and the question mark wildcard for one character. A tilde can be used to treat a wildcard character literally.

COUNTIFS Compared with COUNTIF and SUMIFS

COUNTIF counts cells that meet one condition, while COUNTIFS can count cells using multiple condition pairs. For a single threshold, either function may be suitable. If you expect to add criteria—such as region, category, or date—COUNTIFS makes the expanded logic explicit.

COUNTIFS counts records; it does not add the values that pass the test. If the task is to total sales above a threshold, use SUMIFS instead, with the sales values as the sum range and the appropriate criteria ranges and criteria. If you want the average of qualifying values, AVERAGEIFS may be appropriate. Choosing the function that matches the requested result prevents a frequent mix-up between counting qualifying rows and summing their amounts.

Use COUNTIFS when you need a count and all the criteria must be satisfied. Choose COUNTIF for a simple one-condition count if that makes the worksheet easier to read. Choose SUMIFS or AVERAGEIFS when the result should be a total or average rather than a count.

Common Errors and Ways to Fix Them

  • Missing quotation marks: A criterion such as greater than 100 must be written as a text comparison, with quotation marks around the operator and fixed number.
  • Missing concatenation: When the threshold is in a cell, join the quoted operator to the reference with &. Putting the cell reference inside the quoted text makes it literal text rather than a live reference.
  • Ranges with different sizes: Criteria ranges should have matching dimensions. For example, pairing B2:B10 with C2:C9 can produce a #VALUE! error. Align the start and end rows.
  • Numbers stored as text: A cell that looks like 125 may actually contain text. Check the source data and convert text numbers to numeric values if the count is unexpectedly low.
  • Dates stored as text: A displayed date is not necessarily a true Excel date. If date comparisons fail, check whether the cells contain date values rather than text strings.
  • Unexpectedly excluding equals: The > operator excludes values exactly equal to the threshold. Use >= when equality should count.
  • Hidden rows: COUNTIFS counts matching cells even if their rows are hidden or filtered out. If you need a count that changes with filtered visibility, COUNTIFS by itself does not provide that behavior.

COUNTIFS comparisons are not case-sensitive for ordinary text criteria. Also, blank cells do not satisfy a numeric greater-than test. If results still seem wrong, inspect a few source cells directly and confirm that the criteria range covers the intended records and that the threshold is the number you expect.

Tips for Building a Reliable Count

Before writing the formula, decide precisely what “greater than” means in the question: strictly above the cutoff, or at least the cutoff. Then identify the cells holding the values, any other conditions, and whether a threshold should be fixed or entered in a worksheet cell. These small choices prevent many off-by-one or wrong-range results.

  • Use a cell reference for a threshold that users will change often.
  • Keep each criteria range aligned to the same set of records.
  • Test the formula against a small, easy-to-check sample, especially around the cutoff.
  • Use an actual numeric threshold for numbers and valid Excel date values for dates.
  • Label the result clearly, such as “Sales above target,” so the strictness of the comparison is apparent.

For large workbooks, avoid selecting entire columns automatically when the data is structured and the workbook contains many formulas. Bounded ranges or Excel tables can make the intended data area clearer. If your source data is an Excel table, structured references can make formulas easier to maintain as rows are added, though the same criteria rules still apply.

Frequently Asked Questions

How do I count values greater than or equal to a number?
Use the >= operator in the COUNTIFS criterion. The equals sign matters: > excludes values that match the cutoff, while >= includes them.

How do I use a cell reference instead of typing the number?
Put the operator in quotation marks and join it to the threshold cell with an ampersand. This allows the count to update when you change the threshold cell.

Can COUNTIFS count values above a threshold in one category only?
Yes. Add a criteria range and criterion for the category, followed by the numeric range and greater-than criterion. A row counts only when both tests are true.

Why does my COUNTIFS formula return zero?
Check the comparison operator and quotation marks, verify the threshold, and confirm that the values are real numbers rather than numbers stored as text. For date tests, check that the source cells contain real Excel dates.

Does COUNTIFS ignore filtered or hidden rows?
No. It counts every matching cell in its ranges, including cells in filtered-out or manually hidden rows. COUNTIFS is therefore not a visible-rows-only counting function.

Should I use COUNTIF or COUNTIFS?
For one condition, COUNTIF is a straightforward choice. COUNTIFS is useful when you have multiple conditions or want a formula that can be extended with additional criteria pairs.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.