COUNTIF in Excel: 7 Examples That Cover Most Jobs
Last updated June 2026
Quick answer
COUNTIF counts the cells in a range that meet one condition: =COUNTIF(B2:B51,"High") returns how many cells in B2:B51 equal High. The criteria can be text, a number, a comparison like ">100", a date, or a wildcard pattern. For two or more conditions at once, use COUNTIFS. The seven examples below cover almost every counting job you'll hit.
The syntax is always =COUNTIF(range, criteria). The range is the cells you're scanning; the criteria is the test each cell must pass. Criteria is case-insensitive and wrapped in quotes whenever it contains text or a comparison operator. Work through the seven examples in order — each one adds a technique you'll reuse.
How do you use COUNTIF? Seven jobs, in order
Count cells that exactly match text
Point COUNTIF at a column of labels and pass the word in quotes. This counts every row marked High in the priority column
B2:B51. Matching ignores case, soHigh,high, andHIGHall count.Count numbers above a threshold
Put the comparison operator inside the quotes with the number. This counts every value in
E2:E200greater than 100 — use>=,<, or<=the same way.Count dates before today
Join the operator to
TODAY()with&so Excel builds the criteria string at calc time. This counts dates inC2:C51earlier than the current date — overdue items, in other words.Count cells containing a word (wildcards)
Wrap the text in
*asterisks to match anywhere in the cell. This counts every cell inA2:A200that contains invoice —INV-invoice-204,Old invoice, andinvoiceall count. Use?to match a single character.Count non-blank cells
Pass
"<>"— the "not equal to nothing" operator — to count every cell that has any content. This counts filled cells inA2:A51, including text, numbers, and formula results.Count rows matching two conditions (COUNTIFS)
COUNTIFS takes range/criteria pairs and counts rows where every test passes. This counts rows that are High priority in
B2:B51and not yet done inD2:D51— your open high-priority work.Count everything except one value
Combine the
<>operator with a value to count by exclusion. Watch the blank-cell trap: bare=COUNTIF(B2:B51,"<>Low")also counts every empty cell, because a blank is "not equal to Low" — on a mostly-emptyB2:B51it returns the row count, not your real total. Add a non-blank gate with COUNTIFS so it counts only filled cells that aren't Low — the High-plus-Medium group, without listing each label.
| What you want to count | Formula | Note |
|---|---|---|
| Rows that exactly equal a word | =COUNTIF(B2:B51,"High") | Case-insensitive; quotes required for text. |
| Numbers above a threshold | =COUNTIF(E2:E200,">100") | Operator goes inside the quotes with the number. |
| Dates before today | =COUNTIF(C2:C51,"<"&TODAY()) | Join the operator to TODAY() with &. |
| Cells containing a word | =COUNTIF(A2:A200,"*invoice*") | * matches any text; ? matches one character. |
| Cells that aren't empty | =COUNTIF(A2:A51,"<>") | <> alone means "not blank". |
| Rows meeting two conditions | =COUNTIFS(B2:B51,"High",D2:D51,"<>✓") | COUNTIFS adds range/criteria pairs. |
| Everything except one value | =COUNTIFS(B2:B51,"<>Low",B2:B51,"<>") | Counts by exclusion; the <> gate drops blanks, which bare "<>Low" would wrongly count. |
Anchor your range with $ (for example $B$2:$B$51) before you copy a COUNTIF across a summary block. Otherwise the range shifts with each paste and the counts go wrong.
Frequently asked questions
- How do I use a cell reference as the COUNTIF criteria?
- For an exact match, just point at the cell:
=COUNTIF(B2:B51,F1)counts cells equal to whatever is inF1. To combine a reference with an operator, join them with&—=COUNTIF(E2:E200,">"&F1)counts values greater than the number inF1. - Is COUNTIF case-sensitive?
- No — COUNTIF treats High and high as the same value. For a case-sensitive count, use
=SUMPRODUCT(--EXACT(B2:B51,"High")), whereEXACTcompares letter case andSUMPRODUCTtotals the exact matches. - Can COUNTIF count cells that contain errors?
- COUNTIF can't test for errors directly, but
=SUMPRODUCT(--ISERROR(A2:A51))counts every cell holding an error like#N/Aor#DIV/0!.ISERRORreturns TRUE for errors andSUMPRODUCTadds the TRUEs up. - Why does COUNTIF return 0 when I can clearly see matching cells?
- Almost always a trailing space or a text/number mismatch.
"High "with a space won't match"High", and the number 100 stored as the text"100"won't match">100". Clean the column with Text to Columns orTRIMand retest.