Master Conditional Counts with COUNTIF and COUNTIFS (Text, Dates, Duplicates, and Unique Values in 1 Minute)
Whenever you need to know “How many?”—use COUNTIF/COUNTIFS to count accurately. This guide covers text, dates, multiple conditions, and counting duplicates or unique values.
Basic Syntax and Differences
| Function | Syntax | Key Point |
|---|---|---|
| COUNTIF | =COUNTIF(range, criteria) | One condition; supports text criteria and wildcards |
| COUNTIFS | =COUNTIFS(range1, criteria1, [range2, criteria2], ...) | Lists AND conditions in range/criteria pairs |
TIP: All ranges must be the same size (same number of rows and columns). For date and number comparisons, join the operator string and value with &.
Single Condition — COUNTIF
Example 1) Status is “Done”
=COUNTIF(D2:D500, "Done")Example 2) Amount ≥ 50,000
=COUNTIF(C2:C500, ">=50000")Example 3) Nonblank items
=COUNTIF(A2:A500, "<>")Multiple Conditions — COUNTIFS (AND/OR)
Example 4) Region=Seoul AND Category=Keyboard
=COUNTIFS(B2:B500,"Seoul", A2:A500,"Keyboard")Example 5) OR condition (add two counts)
=COUNTIFS(A2:A500,"Keyboard") + COUNTIFS(A2:A500,"Mouse")In Excel 365, use a list and sum the results:
=SUM(COUNTIFS(A2:A500, F2:F5)) // spill rangeText Contains, Excludes, and Wildcards
Example 6) Contains “Pro”
=COUNTIF(A2:A500, "*Pro*")Example 7) Excludes “Refurbished”
=COUNTIF(D2:D500, "<>*Refurbished*")Example 8) A code with exactly 3 characters
=COUNTIF(A2:A500, "???")Count a Date Range
Example 9) 2025-08-01 through 2025-08-31
=COUNTIFS(B2:B500, ">="&DATE(2025,8,1), B2:B500, "<="&DATE(2025,8,31))Example 10) Number of orders in the reference month (F2)
=COUNTIFS(B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))Check Duplicates and Count Unique Values
Example 11) Check for duplicates (by row)
=COUNTIF(A:A, A2) > 1 // TRUE indicates a duplicateExample 12) Count unique values (365)
=COUNTA(UNIQUE(A2:A500))Example 13) Count unique values (traditional formula)
=SUM(1/COUNTIF(A2:A500, A2:A500)) // array formulaExample 14) Case-sensitive count
=SUMPRODUCT(--EXACT("Pro", A2:A500))Common Mistakes and Performance Tips
- Numbers stored as text → Values aligned to the left may be text. Convert them with
VALUEor standardize the source format. - Blank cells vs. 0 →
"="means blank cells, while=0means cells whose value is 0. - Wildcard conflicts → To find an actual * or ?, use
"~*"or"~?". - Overusing entire-column references → This can reduce performance. Use precise ranges or table references.
- Multiple-condition OR logic → COUNTIFS alone cannot handle it. Add separate counts or sum a spill range.
Summary
| Goal | Common Formula |
|---|---|
| Include/Exclude | COUNTIF(range,"*keyword*") / COUNTIF(range,"<>*keyword*") |
| Date range | COUNTIFS(date,">="&start, date,"<="&end) |
| OR | COUNTIFS(...)+COUNTIFS(...) or SUM(COUNTIFS(...,list)) |
| Unique values | COUNTA(UNIQUE(range)) / traditional formula: SUM(1/COUNTIF(...)) |
FAQ
How are COUNT, COUNTA, and COUNTBLANK different?
COUNT counts numbers only, COUNTA counts nonblank cells, and COUNTBLANK counts blank cells. COUNTIF/S counts cells that meet specified conditions.
Can I apply criteria across multiple columns at the same time?
Add criteria ranges and criteria in pairs to COUNTIFS, such as region + product + status.