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 thatCOUNTIFtreats cells with empty strings (from formulas) as non-blank. - Case sensitivity: As mentioned,
COUNTIFis case-insensitive. For case-sensitive counting, use=SUMPRODUCT(--EXACT("apple",A2:A20)). - Errors in cells:
COUNTIFignores error values in the range, which is usually fine, but if you need to count errors, useCOUNTIF(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”).
- To count how many orders are “Shipped”:
=COUNTIF(B2:B100,"Shipped"). - To display “All shipped” if every order is shipped, use
=IF(COUNTIF(B2:B100,"Shipped")=COUNTA(B2:B100),"All shipped","Not all shipped"). - 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
- Excel Budget Audit Template: How to Build One
- Excel Browser Power Query: What It Is & How to Use
- Excel Blank Calendar Template: How to Get One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- Excel Filter Out Errors: Quick Methods That WorkTo filter out error rows in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- SQL Server String Functions: A Practical GuideSQL Server string functions let you inspect, search, extract, combine, replace, and format text in queries. The most…
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
- Capitalize in Excel Without a Formula: 3 Easy MethodsTo capitalize text in Excel without a formula, use Data > Flash Fill: type the capitalization you want…
- Average in Excel: Formulas, Examples, and Common FixesTo calculate an average in Excel, use =AVERAGE(B2:B10) if the numbers are in cells B2 through B10. The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.