Excel COUNTIF Then Formula: How to Use It

Formulas & functions · Updated November 22, 2026

The direct answer: there is no single “COUNTIF then” function, but you combine COUNTIF with IF to perform an action after counting, like =IF(COUNTIF(A:A,"Paid")>0,"Yes","No"). This formula checks whether any cell in column A contains “Paid” and returns “Yes” if the count is greater than zero, otherwise “No”.

What Does “COUNTIF Then” Mean?

People search “Excel countif then formula” when they want to count cells that meet a condition and then do something with that count—like display a message, trigger another calculation, or highlight a row. Excel doesn’t have a built-in “COUNTIFTHEN” function; instead, you nest COUNTIF inside an IF statement. The IF function evaluates the count result and returns one value if the condition is true and another if false.

For example, if you track sales and want to know if any order exceeded $500, you could use:
=IF(COUNTIF(B2:B100,">500")>0,"High sales exist","No high sales")
This counts how many values in B2:B100 are greater than 500, then returns the message accordingly.

How COUNTIF Works: Syntax and Examples

The COUNTIF function has two arguments: range and criteria. The range is the group of cells to count, and the criteria can be a number, text, expression, or cell reference. You can use wildcards like * for any sequence of characters and ? for a single character.

  • =COUNTIF(A2:A20,"Apple") counts cells exactly equal to “Apple”.
  • =COUNTIF(A2:A20,"A*") counts cells starting with “A”.
  • =COUNTIF(B2:B20,">100") counts cells with values greater than 100.
  • =COUNTIF(C2:C20,"<>"&D1) counts cells not equal to the value in D1.

Note that text criteria are case-insensitive, so “apple” and “APPLE” are treated the same. For case-sensitive counting, you would need SUMPRODUCT with EXACT.

Combining COUNTIF with IF: The “Then” Logic

To add “then” behavior, you place COUNTIF inside an IF function. The general structure is:
=IF(COUNTIF(range, criteria) condition, value_if_true, value_if_false)
The condition is typically >0, =0, or >some_number.

Example: Suppose you have a list of tasks in A2:A10 and you want to check if any task is marked “Done”. With “Done” in B2:B10, use:
=IF(COUNTIF(B2:B10,"Done")>0,"At least one done","None done")
If B2:B10 contains “Done” in any cell, the formula returns “At least one done”.

You can also use the count directly in calculations, like =COUNTIF(A2:A20,"Yes")*10 to multiply the count by 10—no IF needed.

Using COUNTIF with Other Functions: SUM, AVERAGE, and More

Sometimes “then” means you want to sum or average values based on a count condition. For that, use SUMIF or AVERAGEIF instead of COUNTIF. For example, to sum sales for a specific product, use =SUMIF(A2:A20,"Product X",B2:B20). If you need to count unique items, use SUMPRODUCT with COUNTIF like this:
=SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20))
This counts the number of distinct entries in A2:A20.

For conditional sums based on multiple criteria, use COUNTIFS (plural). For example, =COUNTIFS(A2:A20,"Red",B2:B20,">10") counts rows where column A is “Red” and column B is greater than 10.

Common Problems and How to Fix Them

  • Criteria with text and wildcards: If your criteria contains an asterisk or question mark as a literal character, use a tilde (~) before it, like "~*".
  • Criteria referencing a cell: Use & to concatenate, e.g., =COUNTIF(A2:A20,">"&D1).
  • Counting blank or non-blank cells: Use "" for blanks and "<>" for non-blanks, but note that COUNTIF treats cells with empty strings (from formulas) as non-blank.
  • Case sensitivity: As mentioned, COUNTIF is case-insensitive. For case-sensitive counting, use =SUMPRODUCT(--EXACT("apple",A2:A20)).
  • Errors in cells: COUNTIF ignores error values in the range, which is usually fine, but if you need to count errors, use COUNTIF(range,"#N/A").

COUNTIF vs. COUNTIFS vs. SUMPRODUCT

COUNTIF handles one condition; COUNTIFS handles multiple conditions across ranges. SUMPRODUCT is more flexible and can perform array calculations, but it’s slower on large datasets. For example, to count rows where column A is “Yes” and column B is “No”, you could use =COUNTIFS(A2:A20,"Yes",B2:B20,"No") or =SUMPRODUCT((A2:A20="Yes")*(B2:B20="No")). The COUNTIFS version is more readable and faster.

When you need to count based on a condition that involves a calculation (like counting cells where the difference between two columns exceeds 10), SUMPRODUCT is the way to go: =SUMPRODUCT((B2:B20-C2:C20>10)*1).

Practical Examples with Step-by-Step Layout

Let’s work through a real scenario. Assume you have a list of orders in A2:A100 (order IDs) and statuses in B2:B100 (“Shipped”, “Pending”, “Cancelled”).

  1. To count how many orders are “Shipped”: =COUNTIF(B2:B100,"Shipped").
  2. To display “All shipped” if every order is shipped, use =IF(COUNTIF(B2:B100,"Shipped")=COUNTA(B2:B100),"All shipped","Not all shipped").
  3. To calculate the percentage of shipped orders: =COUNTIF(B2:B100,"Shipped")/COUNTA(B2:B100) and format as percentage.

If you want to check if any order is pending and then apply a discount, you could use =IF(COUNTIF(B2:B100,"Pending")>0,0.1,0) to return a 10% discount flag.

Frequently Asked Questions

Can I use COUNTIF with a cell reference for the criteria? Yes, just reference the cell directly, e.g., =COUNTIF(A2:A20,D1). For operators, use concatenation: =COUNTIF(A2:A20,">"&D1).

How do I count cells that contain specific text, not exact match? Use wildcards: =COUNTIF(A2:A20,"*text*") counts cells that contain “text” anywhere.

Why does my COUNTIF return 0 when I see matching values? Check for extra spaces in the cells or criteria, or that the criteria is not typed with quotes incorrectly. Use =TRIM to clean data.

Can COUNTIF work with dates? Yes, use date criteria like ">=1/1/2023" or reference a date cell: =COUNTIF(A2:A20,">="&D1) where D1 is a date.

Is there a way to count unique values in a column? Yes, use =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)), but be careful with blank cells—they cause division by zero. Use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20)) to ignore blanks.

What’s the difference between COUNTIF and COUNT? COUNT counts numeric cells in a range, while COUNTIF counts cells that meet a specific condition, regardless of data type.

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.