Excel COUNTIF Over 1: Formula & Examples

Formulas & functions · Updated November 22, 2026

The direct answer: use =COUNTIF(range,">1") to count cells in a range whose value is greater than 1. For example, =COUNTIF(A2:A10,">1") returns the number of cells in A2:A10 that are numerically greater than 1.

What COUNTIF Does and How It Works

COUNTIF is a built-in Excel function that counts cells in a range that meet a single condition. Its syntax is =COUNTIF(range, criteria). The range is the group of cells you want to check; the criteria is the condition, written as text, a number, or an expression. For “over 1,” the criteria is the text string ">1". Excel interprets the quotation marks as part of the condition, not as literal text to match.

The function counts only numeric values. Text cells, blank cells, and cells with errors are ignored unless the criteria specifically targets them. For instance, if A2:A10 contains {1, 2, 3, “apple”, blank, 0.5, 4, 1.01, 100}, the formula returns 5 (2, 3, 4, 1.01, 100). The cell with “apple” and the blank are not counted.

How to Write the “Over 1” Criteria

The criteria can be written in several ways, depending on whether you want a hard-coded number or a reference to another cell:

  • Hard-coded: =COUNTIF(B2:B20,">1") — counts all values strictly greater than 1.
  • Cell reference: =COUNTIF(B2:B20,">"&D1) — if D1 contains the number 1, this counts values greater than the value in D1. The ampersand (&) concatenates the comparison operator with the cell reference.
  • Greater than or equal to 1: =COUNTIF(B2:B20,">=1") — counts 1 as well as values above it.
  • Less than or equal to 1: =COUNTIF(B2:B20,"<=1") — counts 1 and values below it.

For the "over 1" question, use ">1" (strictly greater) unless you explicitly want to include 1 itself, in which case use ">=1".

Common Mistakes and How to Avoid Them

Many users accidentally count cells with text that looks like numbers, or they forget that the criteria must be in quotes. Here are the most frequent pitfalls:

  • Forgetting quotes: Writing =COUNTIF(A1:A10,>1) causes a formula error. Always enclose the comparison operator and number in double quotes: ">1".
  • Using a cell reference without concatenation: =COUNTIF(A1:A10,">D1") treats ">D1" as literal text and returns 0. Use ">"&D1 instead.
  • Counting text numbers: If a cell contains the text "2" (entered with an apostrophe or from a text import), COUNTIF may not count it as a number. Use =COUNTIF(A1:A10,">1") — it will ignore text-based numbers. To include them, convert the column to numbers using the Text to Columns feature or by multiplying by 1.
  • Blank cells: Blank cells are never counted as "over 1" because they are not numeric. If you need to count blanks separately, use =COUNTBLANK(range).

Using COUNTIF with Other Conditions (COUNTIFS)

If you need to count cells that are over 1 AND meet another condition, use COUNTIFS. The syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, with dates in A2:A20 and sales in B2:B20, to count sales over 1 that occurred in January (say dates in January 2025):

Assume dates are in A2:A20 and sales in B2:B20. Use:

=COUNTIFS(A2:A20,">=1/1/2025",A2:A20,"<=1/31/2025",B2:B20,">1")

This returns the number of rows where the date falls in January 2025 and the sales value is greater than 1. Note that dates must be entered as date serial numbers; using text dates may cause errors. COUNTIFS works in Excel 2010 and later, including Microsoft 365.

Alternatives: SUMPRODUCT and FILTER

For more flexibility, especially when you want to sum or return values, consider SUMPRODUCT or the newer FILTER function.

  • SUMPRODUCT: =SUMPRODUCT(--(A2:A10>1)) — the double unary (--) converts TRUE/FALSE to 1/0, then SUMPRODUCT sums them, giving the count. This works in all Excel versions.
  • FILTER (Microsoft 365 or Excel 2021): =ROWS(FILTER(A2:A10,A2:A10>1)) — FILTER returns an array of values greater than 1, and ROWS counts the rows. This is more dynamic but requires a newer Excel version.

For summing values over 1, use =SUMIF(A2:A10,">1") which adds the actual values, not just counts them.

Excel Versions and Compatibility

COUNTIF is available in every version of Excel from Excel 2007 onward, including Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web. The formula syntax is identical across Windows, Mac, and web versions. However, on Mac, the keyboard shortcut for entering a formula is the same (press Enter), but the shortcut for copying a formula down is Cmd+D instead of Ctrl+D.

If you are using Excel for the web, the function works the same, but you may need to use semicolons as argument separators if your regional settings use them (e.g., =COUNTIF(A2:A10;">1")). In the desktop version, commas are standard.

Frequently Asked Questions

Does COUNTIF count cells with text like "over 1"? No. COUNTIF only counts cells that match the criteria. If you write =COUNTIF(A1:A10,">1"), it counts numeric values greater than 1, not text. If you have text like "2 apples", it is ignored.

How do I count cells greater than 1 but less than 10? Use COUNTIFS: =COUNTIFS(A1:A10,">1",A1:A10,"<10"). This counts values that are strictly greater than 1 and strictly less than 10.

Why is my COUNTIF returning 0 when I know there are values over 1? The most likely cause is that the cells contain text numbers or are formatted as text. Select the column, go to Data > Text to Columns, and finish with the General format to convert them. Also check that the criteria is correctly quoted.

Can I COUNTIF based on a cell reference that contains the number 1? Yes, use =COUNTIF(A1:A10,">"&B1) where B1 contains the number 1. The ampersand concatenates the greater-than symbol with the value in B1.

Does COUNTIF work with dates? Yes, but dates must be entered as date serial numbers. For example, =COUNTIF(A1:A10,">"&DATE(2025,1,1)) counts dates after January 1, 2025. If you type the date as text, it may not match.

Is there a way to count cells that are not equal to 1? Use =COUNTIF(A1:A10,"<>1"). This counts all cells that are not equal to 1, including text and blanks (blanks are not counted). If you want to exclude blanks, use =COUNTIF(A1:A10,"<>1")-COUNTBLANK(A1:A10).

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.