sodcalculator

10 Tech How-To

COUNTIF Greater Than: Excel Step-by-Step Guide

Guide 10-56578 · Updated · Step by step

This guide shows you how to use the COUNTIF function in Excel to count cells that contain a number greater than a specific value. You’ll learn the exact formula syntax, how to reference a cell for the threshold, and how to handle common errors. No prior Excel formula experience is needed, just a spreadsheet with numeric data.

What You’ll Need

  • A computer with Microsoft Excel (2010 or later, including Microsoft 365).
  • A spreadsheet containing the numeric data you want to count.
  • The specific number you want to count greater than (e.g., 100, 5000, or a cell reference containing that number).

Step-by-Step

  1. Open your Excel workbook and navigate to the worksheet that contains the data you want to analyze.
  2. Identify the range of cells you want to count. For example, if your numbers are in cells A2 through A100, your range is A2:A100.
  3. Click on an empty cell where you want the result to appear. This is often next to your data or in a summary area.
  4. Type the formula: =COUNTIF(A2:A100,">100"). Replace A2:A100 with your actual range and 100 with your threshold number.
  5. Press Enter. Excel will display the count of cells in the range that are greater than 100.
  6. To use a cell reference for the threshold (so you can change the number without editing the formula), type =COUNTIF(A2:A100,">"&B1), where B1 contains your threshold number. The ampersand (&) concatenates the greater-than symbol with the value in B1.
  7. For greater than or equal to, use >= instead of >. Example: =COUNTIF(A2:A100,">=100").
  8. Copy the formula to other cells if needed, using relative or absolute references as appropriate (e.g., $A$2:$A$100 to lock the range).

Common Mistakes

  • Forgetting the quotes: The criteria must be enclosed in double quotes, like ">100". Without them, Excel may return an error or treat it as a text comparison.
  • Using a comma instead of a semicolon: In some regional settings, Excel uses semicolons (;) as argument separators. If you get an error, try =COUNTIF(A2:A100;">100").
  • Including the header row: If your range includes a text header, COUNTIF will ignore it (since it’s not numeric), but it’s cleaner to exclude it to avoid confusion.
  • Mixing up > and >=: Decide whether you want to include the threshold number itself. >100 excludes 100, >=100 includes it.
  • Using a cell reference without the ampersand: Writing ">B1" will look for cells greater than the text “B1”, not the value in B1. Always use ">"&B1.

Tips

  • Use absolute references when copying the formula to multiple cells. For example, =COUNTIF($A$2:$A$100,">"&B1) keeps the range fixed while allowing B1 to change.
  • Combine with other functions: Use COUNTIFS for multiple conditions, e.g., count cells greater than 100 but less than 200: =COUNTIFS(A2:A100,">100",A2:A100,"<200").
  • Count non-numeric text: COUNTIF can also count text entries, but for greater-than comparisons, only numeric values are counted.
  • Use a helper cell for the threshold: This makes your spreadsheet easier to update and less error-prone.
  • Check for hidden characters: If your numbers are stored as text (e.g., from imported data), COUNTIF may not count them. Convert text to numbers using the VALUE function or Text to Columns.

Troubleshooting

Formula returns 0 even though there are clearly numbers greater than the threshold. Check if the cells contain numbers stored as text. Select the range, look for a warning icon (yellow diamond) and choose "Convert to Number". Alternatively, use =COUNTIF(A2:A100,">"&B1) and ensure B1 is a number, not text.

Formula returns a #NAME? error. This usually means you typed the function name incorrectly or used a comma instead of a semicolon in your region. Double-check the spelling and try using semicolons.

Formula returns #VALUE! error. This can happen if your range contains errors (like #DIV/0!). COUNTIF ignores errors in the range, but if the criteria itself is invalid, you'll get this error. Ensure the criteria is a valid comparison string.

The count seems too high or low. Verify that your range doesn't include extra rows or columns. Use the Name Manager (Formulas tab) to check named ranges.

COUNTIF doesn't count dates correctly. Dates are stored as serial numbers in Excel. To count dates greater than a certain date, use the DATE function: =COUNTIF(A2:A100,">"&DATE(2024,1,1)).

Frequently Asked Questions

Q: Can I use COUNTIF to count cells greater than a number in another worksheet?
A: Yes, reference the other sheet in the range, e.g., =COUNTIF(Sheet2!A2:A100,">100").

Q: How do I count cells greater than a number but less than another number?
A: Use COUNTIFS with two criteria: =COUNTIFS(A2:A100,">100",A2:A100,"<200").

Q: Does COUNTIF work with decimal numbers?
A: Yes, it works with any numeric value, including decimals. Just set your threshold accordingly.

Q: Can I use a cell reference for the range as well?
A: You can use a named range or an INDIRECT function, but the range must be a valid reference. For example, =COUNTIF(INDIRECT("A2:A100"),">100").

Q: What if my data has blank cells?
A: Blank cells are ignored by COUNTIF. They won't affect the count.

Q: Is there a way to count cells greater than a number excluding zeros?
A: Yes, use =COUNTIF(A2:A100,">0") to count positive numbers, or =COUNTIFS(A2:A100,">0",A2:A100,">"&your_threshold) for a custom threshold.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →