COUNTIF Function in Excel: Syntax, Examples & Tips 2026

COUNTIF counts the cells in a range that meet one condition, using the syntax =COUNTIF(range, criteria). For example, =COUNTIF(B2:B100, "Shipped") returns how many cells in B2:B100 equal "Shipped", ignoring upper and lower case. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).

The COUNTIF function in Excel is one of the most useful tools you'll reach for on a daily basis. It counts cells in a range that meet a single condition — no complex setup, no helper columns. Just one formula that answers questions like "how many times does 'Approved' appear in this column?" or "how many sales exceeded $500?"

Before you dig into advanced formulas, it helps to have a solid grasp of the Excel definition and core concepts. But even beginners can start using COUNTIF immediately. The syntax is straightforward, and the logic is intuitive once you see a few examples.

This guide covers everything — basic syntax, wildcard matching, comparison operators, date counting, the multi-criteria sibling COUNTIFS, and the mistakes that trip people up most often.

Microsoft Excel Exam at a Glance

Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.

Free Excel Study Tools

Interactive Excel resources on PracticeTestGeeks, free to start:

• Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
• Excel flashcards — review key terms and concepts card by card
• Excel study guide — topic-by-topic overview of what the exam covers
• Excel cheat sheet — one-page printable summary for last-minute review

COUNTIF Syntax: Breaking It Down

The full syntax is: =COUNTIF(range, criteria)

Two arguments. That's it.

• range — the group of cells you want to check. This can be a column like A2:A100, a row, or a named range.
• criteria — the condition a cell must meet to be counted. It can be a number, text string, cell reference, or logical expression.

Excel scans each cell in the range, checks whether it matches the criteria, and returns a count of matching cells. It's case-insensitive by default — "apple" and "Apple" both count the same way.

One limitation worth knowing upfront: COUNTIF only handles a single condition. If you need two or more conditions applied simultaneously, you'll use COUNTIFS (covered below). But for most everyday counting tasks, a single COUNTIF gets the job done fast.

Basic COUNTIF Examples

Let's look at the most common use cases you'll run into.

Count Cells Equal to a Specific Value

Say column B contains order statuses. You want to count how many orders are "Shipped":

=COUNTIF(B2:B100, "Shipped")

The criteria is wrapped in quotes because it's a text string. Excel counts every cell in B2:B100 that contains exactly "Shipped".

Count Using a Cell Reference

Instead of hardcoding the value, point to a cell:

=COUNTIF(B2:B100, E2)

If E2 contains "Shipped", the formula counts the same way. This approach is more flexible — change E2 and the count updates automatically. No formula editing required.

Count Cells Equal to a Number

Numbers don't need quotes:

=COUNTIF(C2:C50, 100)

This counts every cell in C2:C50 that equals exactly 100. You can also quote numbers — "100" — and COUNTIF still works, but it's cleaner without quotes for numeric criteria.

Count Non-Empty Cells

To count all cells that aren't blank:

=COUNTIF(A2:A200, "<>")

The <> operator means "not equal to", and leaving the value blank means "not empty". Handy for data completeness checks.

Count Blank Cells

For blanks specifically:

=COUNTIF(A2:A200, "")

Two quotes with nothing between them = empty string = blank cell. Though for this specific task, COUNTBLANK is also an option.

Wildcard Criteria in COUNTIF

Wildcards let you match partial text — incredibly useful when your data isn't perfectly consistent.

Excel supports two wildcards in COUNTIF criteria:

• Asterisk (*) — matches any sequence of characters (including zero characters)
• Question mark (?) — matches exactly one character

Using the Asterisk (*)

Count all cells containing the word "apple" anywhere:

=COUNTIF(A2:A100, "*apple*")

The asterisks on both sides mean "anything before or after apple". So "apple juice", "green apple", and "pineapple" all match.

Count cells starting with "Pro": =COUNTIF(A2:A100, "Pro*")

Count cells ending with "2024": =COUNTIF(A2:A100, "*2024")

Using the Question Mark (?)

The question mark matches exactly one character:

=COUNTIF(A2:A100, "b?t")

This matches "bat", "bit", "bot", "but" — any three-letter word starting with "b" and ending with "t". Not something you'll use every day, but essential for matching codes with a fixed-length format.

Combining Wildcards with Cell References

Concatenate a wildcard with a cell reference using the ampersand:

=COUNTIF(A2:A100, "*"&E2&"*")

If E2 contains "report", this counts any cell with "report" anywhere in the text. Great for dynamic search-style formulas where users type a keyword and the count updates live.

Matching a Literal Asterisk or Question Mark

If your data contains asterisks or question marks you want to match literally, prefix with a tilde (~):

=COUNTIF(A2:A100, "~*")

The tilde tells Excel to treat what follows as a regular character, not a wildcard.

Comparison Operators in COUNTIF

When you need to count cells based on a value threshold — greater than, less than, equal to — you use comparison operators inside the criteria string.

All comparison operators in COUNTIF must be wrapped in quotes as part of the criteria string:

• > — greater than
• < — less than
• >= — greater than or equal to
• <= — less than or equal to
• <> — not equal to

Practical Examples

Count sales greater than $1,000: =COUNTIF(C2:C200, ">1000")

Count scores of 70 or higher: =COUNTIF(D2:D50, ">=70")

Count items that aren't "Cancelled": =COUNTIF(B2:B100, "<>Cancelled")

Count values below the average — note the concatenation trick:

=COUNTIF(C2:C200, "<"&AVERAGE(C2:C200))

That last one is powerful. You can combine the < operator string with any calculated value using &. The formula builds criteria dynamically at runtime — no hardcoding required.

This same pattern works with cell references: =COUNTIF(C2:C200, ">"&F1)

Where F1 contains your threshold. Change F1, the count updates automatically. If you're building dashboards with dynamic filtering, this operator-plus-reference pattern is something you'll use constantly. Combined with conditional formatting in Excel, you can highlight rows while COUNTIF tracks the totals automatically.

COUNTIF Criteria Cheat Sheet

Each formula below uses the same two arguments, range and criteria, and shows exactly which cells are counted.

COUNTIF with Dates

Dates in Excel are stored as serial numbers under the hood, so COUNTIF can count them using the same comparison operators. The key is formatting your criteria correctly so Excel interprets them as date values, not text strings.

Count Dates Equal to a Specific Date

The safest approach uses the DATE function:

=COUNTIF(A2:A100, DATE(2024, 12, 31))

Avoid typing dates as text strings like "12/31/2024" — regional date format differences can break the formula silently on someone else's machine.

Count Dates After a Certain Date

=COUNTIF(A2:A100, ">"&DATE(2024, 6, 1))

This counts all dates after June 1, 2024. The concatenation trick from the previous section applies here too.

Count Dates Before Today

=COUNTIF(A2:A100, "<"&TODAY())

TODAY() returns the current date as a serial number. This formula counts all past dates and updates automatically each day — no maintenance needed. It's ideal for overdue-item tracking.

Count Dates in a Specific Month

COUNTIF alone can't filter by month cleanly. Use two COUNTIF formulas and subtract:

=COUNTIF(A2:A100, ">="&DATE(2024,3,1)) - COUNTIF(A2:A100, ">="&DATE(2024,4,1))

This counts dates in March 2024 — all dates on or after March 1, minus all dates on or after April 1. For more complex date filtering, COUNTIFS handles it more cleanly (see the next section).

COUNTIFS: Multiple Criteria at Once

COUNTIFS is the multi-condition version of COUNTIF. It counts cells only when ALL specified criteria are met simultaneously.

Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)

You can add as many range/criteria pairs as you need — each range must be the same size as the others.

Basic COUNTIFS Example

Count orders from the "West" region with a value over $500:

=COUNTIFS(B2:B100, "West", C2:C100, ">500")

Both conditions must be true for a row to count. Region = West AND amount > 500. If either condition fails, the row doesn't count.

Count Dates in a Range with COUNTIFS

COUNTIFS makes date-range counting much cleaner:

=COUNTIFS(A2:A100, ">="&DATE(2024,3,1), A2:A100, "<"&DATE(2024,4,1))

Same range, two criteria — both applied. Much more readable than the subtract approach.

Three or More Criteria

Add more range/criteria pairs as needed:

=COUNTIFS(B2:B100, "West", C2:C100, ">500", D2:D100, "Shipped")

Region = West AND amount > 500 AND status = Shipped. All three must be true. COUNTIFS accepts up to 127 range/criteria pairs, and the ranges do not have to be adjacent, but each must have the same number of rows and columns as the first.

COUNTIFS with the Same Range Twice (Between Range)

Reference the same range multiple times to create a between condition:

=COUNTIFS(C2:C100, ">=100", C2:C100, "<=500")

Counts values between 100 and 500 inclusive — the standard pattern for between-range queries. Much cleaner than the subtract approach.

COUNTIF vs COUNTIFS vs SUMIF — Which to Use When

These three functions look similar but answer different questions. Picking the wrong one means extra formula gymnastics for no reason.

Use COUNTIF when you need a count based on one condition. It's the simplest option for frequency analysis — how many times a value appears, how many cells exceed a threshold, how many entries match a status.

Use COUNTIFS when you need a count with two or more conditions that must all be true simultaneously. Any time you catch yourself writing COUNTIF() * COUNTIF(), stop — COUNTIFS is the right tool.

Use SUMIF when you want to add up values based on a condition — not count rows, but produce totals. Total revenue for a specific region, hours logged for a specific employee. SUMIFS handles multiple conditions for summing.

Quick decision rule: if your question starts with "how many" — COUNTIF or COUNTIFS. If it starts with "how much" or involves dollar amounts — SUMIF or SUMIFS.

These functions shine together in data cleanup. After you delete duplicates in Excel, running a COUNTIF on the cleaned data confirms whether any duplication remains — a fast sanity check that takes seconds.

Pros and Cons at a Glance

Common COUNTIF Errors and How to Fix Them

Even a simple function has its gotchas. Here are the errors you'll hit most often and how to solve them quickly.

#VALUE! Error

Microsoft documents this error for COUNTIF when the formula refers to cells in a closed workbook and is recalculated; open the source workbook to fix it. Separately, COUNTIF returns incorrect results (not an error) when you match strings longer than 255 characters. Fix: keep criteria under 255 characters, or build a long string with the & operator, as Microsoft's own example does.

Formula Returns 0 When It Shouldn't

The most common cause: invisible characters in your data. Extra spaces, line breaks, or non-printing characters prevent a match even when the cell looks correct on screen.

Fix: Use TRIM() on your data range. If that doesn't work, also look for straight versus curly quotation marks and non-printing characters, which Microsoft lists as causes of unexpected COUNTIF results. Try CLEAN() as well as TRIM(), or use Text-to-Columns to reformat the column.

Counting Across Multiple Sheets

COUNTIF takes a single range, so a 3D reference across sheets is not a reliable approach. Avoid a formula like this: =COUNTIF(Sheet1:Sheet3!A:A, "Value")

Fix: Write a COUNTIF for each sheet and sum them:

=COUNTIF(Sheet1!A:A, "Value") + COUNTIF(Sheet2!A:A, "Value") + COUNTIF(Sheet3!A:A, "Value")

Or consolidate your data into one sheet first — usually better practice anyway.

Comparison Operator Not Working

Forgetting the quotes around operators is the most common syntax mistake. Wrong: =COUNTIF(C:C, >50). Right: =COUNTIF(C:C, ">50"). The operator and value must be inside quotes.

Count Is 1 Too High

Your range includes the header row and it matches your criteria. Fix: start your range at row 2 (A2:A100 instead of A1:A100).

Advanced COUNTIF Patterns Worth Knowing

Once you're comfortable with the basics, these patterns unlock more powerful analysis without complex formulas.

Count Unique Values

COUNTIF doesn't directly count uniques, but you can use it inside SUMPRODUCT:

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

This works by counting how many times each value appears, then summing the reciprocals — counting each unique value exactly once. Note: this breaks with blank cells, so filter those out first.

Highlight Duplicates Using COUNTIF

In conditional formatting rules, COUNTIF powers duplicate highlighting. A custom rule like =COUNTIF($A$2:$A$100, A2)>1 applied to a range highlights every value that appears more than once.

This is far more flexible than the built-in "Highlight Duplicates" preset. You can pair it with conditional formatting in Excel to create data-quality dashboards that flag issues automatically.

Percentages with COUNTIF

What percentage of your data meets a condition? Divide COUNTIF by COUNTA:

=COUNTIF(B2:B100, "Approved") / COUNTA(B2:B100)

Format the result as a percentage and you've got instant approval rates, completion rates, or pass rates — no separate column needed.

COUNTIF as a Validation Check

After importing data, run COUNTIF to verify expected counts. If you're expecting 50 entries per region but COUNTIF for "North" returns 47, something got dropped. It's a quick audit step that catches problems before they spread through your analysis.

Before building pivot tables, use COUNTIF to verify dropdown list values match actual data. If you've set up how to add drop down list in excel for data entry, a COUNTIF check confirms entries match valid options and none were typed freehand.

Whole-Column References

If your data grows over time, avoid hardcoding the range end row. Use whole-column references like A:A instead of A2:A100. The range then grows automatically as you add rows, but keep header text out of the counted range (or exclude it with your criteria).

To build stronger Excel skills, practice these patterns on real datasets. Replace your manual filter-and-count workflow with COUNTIF formulas — when the results match, you know you've got it right.

Sample Excel Practice Questions

Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.

1. What does the COUNTIF function do in Excel?

   • A. Counts the number of cells in a range that meet a single condition
   • B. Counts all non-empty cells in a range
   • C. Counts the number of unique values in a range
   • D. Counts cells that contain formulas

   Answer: A. Counts the number of cells in a range that meet a single condition

   COUNTIF(range, criteria) counts cells that match a specified condition, such as counting how many cells contain a value greater than 100 or matching a specific text.

2. What does the TRIM function do in Excel?

   • A. Removes all spaces from text
   • B. Removes leading and trailing spaces and reduces internal spaces to one
   • C. Cuts text to a specified length
   • D. Removes numeric characters from text

   Answer: B. Removes leading and trailing spaces and reduces internal spaces to one

   TRIM removes leading and trailing spaces and reduces any internal sequences of spaces to a single space.

3. What is the purpose of the ROUND function in Excel?

   • A. Converts numbers to circular chart data
   • B. Rounds a number to a specified number of digits
   • C. Removes decimal places permanently
   • D. Calculates the circumference of a circular value

   Answer: B. Rounds a number to a specified number of digits

   ROUND(number, num_digits) rounds a number to the specified count of decimal places, rounding up at 5 or above.

Take the full Excel practice test

Putting It All Together

COUNTIF is one of those functions that seems basic until you realize how often you're reaching for it — in audit reports, dashboards, data validation scripts, conditional formatting rules, and quick ad-hoc analysis. It's the difference between manually scrolling a spreadsheet and having your answer in two seconds.

The core pattern is always the same: range, then criteria. Everything else — operators, wildcards, date functions, cell references — is just different ways to express that criteria. Once that clicks, you can build any COUNTIF formula without looking up the syntax.

COUNTIFS extends this naturally when conditions pile up. And when your goal shifts from counting to summing, SUMIF and SUMIFS follow the exact same logic — just a different verb.

If you want to sharpen your formula skills beyond COUNTIF, the Excel certification practice test covers the full range of Excel functions you'd encounter in professional environments — COUNTIF, VLOOKUP, INDEX/MATCH, PivotTables, and more. Working through practice questions is the fastest way to build fluency where formulas become second nature.