COUNTIF Within a Range: How to Count Cells That Meet a Condition

Formulas & functions · Updated November 22, 2026

The direct answer: use the COUNTIF function with a range and a criterion, like =COUNTIF(A2:A100, "Yes"), which counts every cell in A2:A100 that equals “Yes”. This works in Microsoft Excel, Google Sheets, and most other spreadsheet programs, and it is the standard way to count cells that meet a single condition within a specified range.

What COUNTIF Does and Why You Need It

COUNTIF is one of the most frequently used functions in spreadsheets because it answers a simple question: “How many cells in this block of data meet a certain rule?” Without it, you would have to manually scan hundreds or thousands of rows, or write complex array formulas. The function takes exactly two arguments:

  • Range – the group of cells you want to examine (e.g., B2:B50).
  • Criteria – the condition that determines which cells are counted. This can be a number, text, a cell reference, or a logical expression.

For example, if column A contains product names and column B contains quantities sold, you might want to know how many products sold more than 100 units. That formula would be =COUNTIF(B2:B100, ">100"). The function counts only the cells in the range that satisfy the condition, ignoring blanks and cells that don’t match.

How to Write a COUNTIF Formula: Step-by-Step

Writing a COUNTIF formula is straightforward, but the exact syntax matters. In Excel for Microsoft 365 on Windows, follow these steps:

  1. Click the cell where you want the result to appear.
  2. Type =COUNTIF( (the equal sign is required).
  3. Select the range of cells you want to count, or type the range manually (e.g., A2:A20).
  4. Type a comma, then enter your criteria. If the criteria is text, put it in double quotes: "Paid". If it’s a number, you can type it directly: 5.
  5. Close the parenthesis and press Enter.

For example, with names in A2:A20 and statuses in B2:B20, to count how many are “Active”, you would write =COUNTIF(B2:B20, "Active"). If the status is stored as a cell reference (say, D1 contains the word “Active”), you can use =COUNTIF(B2:B20, D1) – no quotes around the cell reference.

On a Mac, the steps are identical. In Excel for the web, the function works the same way, but you type the formula directly into the formula bar. In Google Sheets, the syntax is also identical, though the menu paths for inserting functions differ slightly (you can use Insert > Function > Statistical > COUNTIF).

Using Wildcards and Comparison Operators

COUNTIF becomes far more powerful when you use wildcards and comparison operators. Wildcards are special characters that stand in for other characters:

  • Asterisk (*) – matches any sequence of characters. For example, =COUNTIF(A2:A10, "A*") counts cells that start with the letter “A”.
  • Question mark (?) – matches any single character. For example, =COUNTIF(A2:A10, "B?") counts two-character cells that start with “B”.
  • Tilde (~) – escapes a wildcard, so you can count literal asterisks or question marks. For instance, =COUNTIF(A2:A10, "~*") counts cells that contain an actual asterisk.

Comparison operators work with numbers and dates. To count cells greater than 100, use ">100". For less than or equal to, use "<=100". For not equal to, use "<>". For example, =COUNTIF(B2:B20, "<>Pending") counts all cells that are not exactly “Pending”. Note that when using a comparison operator with a cell reference, you must concatenate the operator with the reference using an ampersand: =COUNTIF(B2:B20, ">"&D1).

Counting with Multiple Conditions: COUNTIFS and Alternatives

Sometimes you need to count cells that meet more than one condition. The COUNTIFS function handles this by accepting multiple range/criteria pairs. For example, to count rows where column A = “Product X” and column B > 50, use =COUNTIFS(A2:A100, "Product X", B2:B100, ">50"). Each additional pair is added after the first, separated by commas.

If you need to count based on a condition that involves a calculation or a function, you might use SUMPRODUCT or array formulas, but for most everyday tasks, COUNTIFS is simpler. For example, to count how many cells in A2:A20 are greater than the average of that same range, you could use =SUMPRODUCT(--(A2:A20>AVERAGE(A2:A20))) – but this is an array operation and requires pressing Ctrl+Shift+Enter in older Excel versions (in Microsoft 365, it works normally).

Common Problems and How to Fix Them

Many users run into issues with COUNTIF that have simple solutions:

  • Criteria with text and spaces – If your data has trailing spaces, COUNTIF won’t match. Use =COUNTIF(A2:A10, "Yes") only if the cells have no extra spaces; otherwise, consider using TRIM in a helper column.
  • Case sensitivity – COUNTIF is not case-sensitive by default. It treats “yes”, “Yes”, and “YES” as the same. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
  • Counting blanks or non-blanks – To count blank cells, use =COUNTIF(A2:A10, ""). To count non-blank cells, use =COUNTIF(A2:A10, "<>"). Note that "<>" counts cells that are not empty, including cells with formulas that return an empty string.
  • Error values – If your range contains errors like #N/A, COUNTIF will ignore them when counting, but if your criteria is an error, you need to use IFERROR or a different approach.

COUNTIF vs. Other Counting Functions

Spreadsheets offer several counting functions, and choosing the right one depends on your goal:

  • COUNT – Counts numeric cells only. =COUNT(A2:A10) counts how many cells contain numbers.
  • COUNTA – Counts all non-empty cells, including text and numbers. =COUNTA(A2:A10) counts every cell that is not blank.
  • COUNTBLANK – Counts empty cells. =COUNTBLANK(A2:A10) gives the number of blank cells.
  • COUNTIF – Counts cells that meet a single condition.
  • COUNTIFS – Counts cells that meet multiple conditions across multiple ranges.

For example, if you have a list of sales figures in B2:B100 and you want to know how many are numbers, use =COUNT(B2:B100). If you want to know how many are above 500, use =COUNTIF(B2:B100, ">500"). If you want to know how many are above 500 and correspond to a specific region in column A, use =COUNTIFS(A2:A100, "West", B2:B100, ">500").

Frequently Asked Questions

Can I use COUNTIF with dates?

Yes. Dates are stored as numbers in Excel, so you can use comparison operators. For example, to count dates after January 1, 2024, use =COUNTIF(C2:C50, ">"&DATE(2024,1,1)). Ensure the cells are formatted as dates, not text, or the comparison may fail.

How do I count cells that contain specific text anywhere?

Use wildcards. For example, =COUNTIF(A2:A20, "*apple*") counts cells that contain the word “apple” anywhere in the text. Remember that COUNTIF is not case-sensitive, so it will also count “Apple” and “APPLE”.

Why is my COUNTIF returning 0 when I know there are matches?

This usually happens because of extra spaces in the data or in the criteria, or because the criteria is a number stored as text. Check for invisible characters by using the LEN function on a sample cell. Also, ensure you’re using the correct operator – if you want exact match, don’t use wildcards accidentally.

Can I count cells based on color or font?

No, COUNTIF cannot count based on formatting like cell color or bold text. For that, you would need to use VBA (in Excel) or a Google Sheets script, or add a helper column with a formula that identifies the condition you care about.

Is there a limit to the number of criteria in COUNTIFS?

In Excel 365, COUNTIFS supports up to 127 range/criteria pairs. In Google Sheets, the limit is also 127. In practice, you rarely need that many, but it’s good to know the limit exists.

Does COUNTIF work with whole columns like A:A?

Yes, you can use entire columns, but be cautious because it will count every non-empty cell in that column, which can slow down your spreadsheet. It’s better to use a defined range like A2:A1000 for performance.

Practical Example: Counting Orders by Status

Let’s put this into a real scenario. Suppose you have an order log with columns: A = Order ID, B = Customer, C = Status (e.g., “Shipped”, “Pending”, “Cancelled”), and D = Order Total. To count how many orders are “Shipped”, you would use =COUNTIF(C2:C500, "Shipped"). To count orders over $100 that are “Shipped”, use =COUNTIFS(C2:C500, "Shipped", D2:D500, ">100"). If you want to count orders that are either “Shipped” or “Pending”, you can add two COUNTIFs: =COUNTIF(C2:C500, "Shipped") + COUNTIF(C2:C500, "Pending"). This is a common pattern when you need to count multiple specific text values.

Remember, COUNTIF is a simple, fast, and reliable function that works across all major spreadsheet applications. Once you master its syntax and the use of wildcards and operators, you’ll find dozens of uses for it in data analysis, reporting, and everyday list management.

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.