Use =COUNTIF(range,">"&A1) to count cells in a range that are greater than the value in cell A1. The ampersand (&) joins the comparison operator to the cell reference, which is required because COUNTIF’s criteria must be a string or a number, not a raw cell reference.
Why the Ampersand Is Necessary
In Excel, the COUNTIF function’s second argument (criteria) is evaluated as text. If you write =COUNTIF(A2:A10,">A1"), Excel reads the literal text “>A1” and looks for cells whose values are greater than the text string “A1″—which is meaningless and returns 0. The ampersand tells Excel to concatenate the greater-than symbol with the actual value stored in A1. For example, if A1 contains 50, then ">"&A1 becomes the text “>50”, which COUNTIF interprets correctly.
Basic Formula Structure and Examples
Assume you have sales numbers in B2:B20 and a threshold value in D1. To count how many sales are greater than D1, use:
=COUNTIF(B2:B20,">"&D1)
If D1 = 100 and B2:B20 contains 150, 80, 120, 90, 110, the formula returns 3 (150, 120, 110). The same logic applies to other operators:
- Greater than or equal:
=COUNTIF(B2:B20,">="&D1) - Less than:
=COUNTIF(B2:B20,"<"&D1) - Less than or equal:
=COUNTIF(B2:B20,"<="&D1) - Not equal:
=COUNTIF(B2:B20,"<>"&D1)
For text criteria, you can use wildcards, but for numeric comparisons the ampersand method is the standard approach.
Using a Cell Reference for Both Range and Threshold
Sometimes you need to compare each cell in a range to a corresponding cell in another column. COUNTIF alone cannot do this row-by-row; you need SUMPRODUCT or an array formula. For example, to count rows where column A is greater than column B (A2:A10 > B2:B10), use:
=SUMPRODUCT(--(A2:A10>B2:B10))
The double unary (--) converts TRUE/FALSE to 1/0, and SUMPRODUCT sums them. This returns the count of rows where A exceeds B. This works in all Excel versions. If you have Excel 365, you can also use =SUM((A2:A10>B2:B10)*1) entered normally, but SUMPRODUCT is more universally compatible.
Common Mistakes and How to Fix Them
A frequent error is forgetting the ampersand, as described above. Another is using quotes around the cell reference: =COUNTIF(A2:A10,">"A1) is invalid syntax. Also, if your threshold cell contains text that looks like a number, COUNTIF may treat it as text and return 0. Ensure the cell is formatted as a number. If you're comparing dates, the same formula works: =COUNTIF(C2:C20,">"&E1) where E1 is a date. For blank cells, COUNTIF ignores them; to count non-blank cells greater than a value, use =COUNTIF(A2:A10,">"&0) only if you want positive numbers, but this also counts text.
Alternatives: COUNTIFS and Dynamic Arrays
If you have multiple conditions, use COUNTIFS with the same ampersand technique. For instance, to count values greater than D1 but less than D2:
=COUNTIFS(B2:B20,">"&D1,B2:B20,"<"&D2)
In Excel 365, you can use the FILTER function to return the actual values, then count them with ROWS:
=ROWS(FILTER(B2:B20,B2:B20>D1))
This returns the count of values greater than D1. FILTER requires Microsoft 365 or Excel 2021. For older versions, SUMPRODUCT is the best alternative for row-by-row comparisons.
Working with Dynamic Thresholds in Tables
If your data is in an Excel Table (Insert > Table), you can use structured references. For a table named Table1 with a column named Sales, and a threshold in cell D1, the formula is:
=COUNTIF(Table1[Sales],">"&D1)
This automatically adjusts as you add rows to the table. The same ampersand rule applies. If you want to compare each row to a value in another column within the same table, you still need SUMPRODUCT, as COUNTIF cannot do per-row comparisons.
Frequently Asked Questions
Can I use COUNTIF with a cell reference without the ampersand?
No. COUNTIF's criteria argument is text, so a raw cell reference like =COUNTIF(A1:A10,A1) works only if you're counting cells equal to the value in A1, not greater than. For comparisons, you must concatenate the operator with the cell reference using &.
How do I count cells greater than a value in another sheet?
Reference the cell normally with the sheet name: =COUNTIF(Sheet2!A1:A10,">"&Sheet2!B1). The ampersand works the same way; just include the sheet qualifier in both the range and the cell reference.
Why does my COUNTIF return 0 even though there are larger numbers?
Check that the threshold cell contains a numeric value, not text. Also, ensure the range doesn't include errors or empty cells that might distort the comparison. If the range includes text, COUNTIF ignores it, but if the threshold is text, all comparisons fail.
Can I use COUNTIF with a formula that returns a value as the threshold?
Yes, you can use a formula inside the ampersand: =COUNTIF(A1:A10,">"&MAX(B1:B5)). This counts cells greater than the maximum of B1:B5. The formula result is converted to text automatically.
What is the difference between COUNTIF and SUMPRODUCT for this?
COUNTIF is simpler and faster for a single range with a single threshold. SUMPRODUCT is needed when you want to compare two ranges row-by-row, or when you need to evaluate multiple conditions without using COUNTIFS. For simple cases, COUNTIF is preferred.
Does this work in Google Sheets?
Yes, the same formula syntax works in Google Sheets: =COUNTIF(A1:A10,">"&B1). The ampersand method is identical. However, Google Sheets also supports array formulas with ARRAYFORMULA, but COUNTIF works the same way.
Related
- How to Select All on a Laptop: 5 Fast Ways
- Excel Merge Cells: How to Combine Cells in Excel
- DB10DL 10.8V Lithium Ion Micro Driver Drill Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Workdays Between Dates: Formulas and ExamplesTo count workdays between two dates in Excel, use NETWORKDAYS. With the start date in A2 and the…
- Generate Invoice Numbers in Excel With a Simple FormulaTo generate invoice numbers in Excel, enter ="INV-"&TEXT(ROW()-1,"0000") in cell A2 when row 1 is a header and…
- Excel Formula for Sales Tax CalculationThe simplest Excel formula for sales tax is =A2*B2 where A2 holds the pre-tax price and B2 holds…
- Flip Names in Excel With a Comma Using a FormulaTo change names such as Mary Ann Smith to Smith, Mary Ann, put the original name in A2…
- PHP String Functions: How to Work With Text SafelyPHP string functions let you inspect, search, change, split, join, and format text. Common choices include strlen() for…
- Excel Formula for Last Name: Extract SurnameThe simplest Excel formula to extract a last name from a full name is =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)), assuming…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.