COUNTIF vs COUNTIFS: Which to Use
COUNTIF counts cells meeting one condition. COUNTIFS counts cells meeting multiple conditions simultaneously. Like SUMIFS, use COUNTIFS for everything — it handles one condition just as well as COUNTIF.
COUNTIF — one condition
=COUNTIF(B:B, "Complete") ← count cells in B that say Complete
=COUNTIF(C:C, ">100") ← count cells in C greater than 100
=COUNTIF(A:A, "apple*") ← count cells in A that start with "apple"
COUNTIFS — multiple conditions
=COUNTIFS(A:A, "East", B:B, "Complete")
COUNTIFS With Date Range
Count items in the last 30 days
=COUNTIFS(A:A, ">="&(TODAY()-30))
=COUNTIFS(A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,12,31)) ← specific year
COUNTIF With Partial Text (Wildcard)
Count cells containing a word
=COUNTIF(A:A, "*apple*") ← contains "apple" anywhere
=COUNTIF(A:A, "apple*") ← starts with "apple"
=COUNTIF(A:A, "*apple") ← ends with "apple"
COUNTIF With OR Logic
COUNTIFS uses AND logic. For OR, add separate COUNTIF results:
Count rows where status is Complete OR In Review
=COUNTIF(B:B,"Complete") + COUNTIF(B:B,"In Review")
Count Unique Values
Count distinct unique values in a column
=COUNTA(UNIQUE(A2:A100))
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
COUNTIF vs COUNT vs COUNTA
| Function | Counts | Example |
|---|---|---|
| COUNT | Cells containing numbers only | =COUNT(A:A) |
| COUNTA | All non-empty cells (text, numbers, dates) | =COUNTA(A:A) |
| COUNTBLANK | Empty cells | =COUNTBLANK(A:A) |
| COUNTIF | Cells meeting one condition | =COUNTIF(A:A,"East") |
| COUNTIFS | Cells meeting multiple conditions | =COUNTIFS(A:A,"East",B:B,">0") |
FAQ
Frequently Asked Questions
How do I count cells that meet two conditions in Excel?▾
Use COUNTIFS: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2). Example: =COUNTIFS(A:A,"East",B:B,"Paid") counts rows where column A is East AND column B is Paid.
How do I count cells that contain specific text in Excel?▾
Use COUNTIF with wildcards: =COUNTIF(A:A,"*apple*") counts all cells in column A that contain the word apple anywhere in the text. The asterisk * matches any characters before or after.
How do I count cells not equal to a value?▾
Use the <> operator: =COUNTIF(B:B,"<>Complete") counts cells in column B that are NOT equal to Complete. For multiple exclusions: =COUNTIFS(B:B,"<>Complete",B:B,"<>Cancelled").
What is the fastest way to generate COUNTIF formulas?▾
Use ExcelForm at excelform.xyz — describe what you want to count in plain English, for example 'count rows where status is not Cancelled and date is in the last 30 days'. ExcelForm generates the COUNTIFS formula with correct syntax instantly.