FormulaSmith
Free tools

COUNTIF

COUNTIF(range, criteria) counts the cells in range that match one condition, with the operator and the value packed into one criteria argument. That packing is what trips people up: it is =COUNTIF(A2:A100,">100") with the whole comparison in quotes, and comparing against a cell means joining with an ampersand, =COUNTIF(A2:A100,">"&B1). Text matching is case-insensitive and matches the entire cell unless wildcards are added, so "apple" will not find "apple pie" but "*apple*" will. A second condition on a second range is beyond COUNTIF entirely — that is COUNTIFS.

Back to FinanceAll guides

The formula

=COUNTIF(A2:A100,"Paid")                   exact text, case-insensitive, whole cell must match
=COUNTIF(A2:A100,">100")                   operator and value together, inside the quotes
=COUNTIF(A2:A100,">"&B1)                   compare against a cell - concatenate the operator with &
=COUNTIF(A2:A100,"*paid*")                 contains, rather than equals
=COUNTIF(A2:A100,"<>Paid")                 everything except that value
=COUNTIF(A2:A100,"<>")                     every non-empty cell
=COUNTIF(A2:A100,B1)                       a bare cell reference needs no quotes at all
=COUNTIFS(A2:A100,"Paid",B2:B100,">30")    two conditions - COUNTIF has nowhere to put the second

A worked example

A2:A16 holds fifteen support tickets' status text: six cells read "Open", five read "Closed", three read "open" in lower case, and one cell is empty. B2:B16 holds each ticket's age in days.

=COUNTIF(A2:A16,"Open")

9, not the 6 you might have expected — COUNTIF ignores case, so the three lower-case "open" cells match as well. =COUNTIF(A2:A16,"<>") returns 14, counting everything but the empty cell. Adding a second test on age is where COUNTIF runs out of arguments: =COUNTIFS(A2:A16,"Open",B2:B16,">30") is the only way to ask both questions in one formula.

Which one do I need?

If you want to…Use
One condition, matching text exactly=COUNTIF(range,"Paid") — case-insensitive, but the entire cell has to match
Greater than, less than, or betweenOperator inside the quotes: =COUNTIF(range,">100"). "Between" is two conditions, so it needs COUNTIFS.
The cell contains a word rather than equals itWildcards: =COUNTIF(range,"*invoice*"). An asterisk matches any run of characters, a question mark matches exactly one.
Two or more conditions at once=COUNTIFS(range1,crit1,range2,crit2) — the plural function, with every range the same height
Dates falling inside a windowTwo bounds means COUNTIFS: =COUNTIFS(range,">="&DATE(2026,1,1),range,"<="&DATE(2026,3,31)) — build the dates with DATE rather than typing them as text
Everything except one value=COUNTIF(range,"<>Paid") — the <> operator goes inside the quotes alongside the value
Counting by fill colourCOUNTIF cannot see formatting at all. Filter by colour and read =SUBTOTAL(103,range), or record the colour in a helper column and count that.
You want the total of the matching rows, not how many there are=SUMIF(range,criteria,sum_range) — identical criteria syntax, one extra argument for the numbers to add
You are not certain COUNTIF is the right count function in the first placeCOUNT, COUNTA and COUNTBLANK answer different questions and need no criteria at all

Frequently asked questions

Why does COUNTIF return 0 when I can see matching cells?

Almost always a mismatch Excel will not show you: a trailing space in the data, numbers stored as text being tested against a numeric criteria, or a criteria that only matches part of the cell. Confirm it with =COUNTIF(range,"*value*") — if the wildcard version finds them, the data needs TRIM or a text-to-number conversion, not a different formula.

How do I use a cell reference inside COUNTIF criteria?

A bare reference needs no quotes: =COUNTIF(A2:A100,B1). Adding an operator means building the criteria as a string with &, giving =COUNTIF(A2:A100,">"&B1). Writing =COUNTIF(A2:A100,">B1") instead asks Excel to count cells greater than the literal text "B1", which returns 0 and raises no error.

Is COUNTIF case-sensitive?

No, and there is no argument to make it so — "Open", "open" and "OPEN" are one value as far as COUNTIF is concerned. For a case-sensitive count use =SUMPRODUCT(--EXACT(range,"Open")), which compares every cell with EXACT and adds up the TRUE results.

Can COUNTIF read a range in a closed workbook?

Another sheet is fine — =COUNTIF(Sheet2!A2:A100,"Paid") behaves normally. A closed external workbook is not: COUNTIF, COUNTIFS and SUMIF all return #VALUE! against a file that is not open, because they need the source in memory. Open the source workbook, or rewrite the count with SUMPRODUCT, which does work across closed files.

COUNTIF Multiple Criteria1900How to Use COUNTIF in Excel1600COUNTIF Greater Than880How to Use COUNTIF Function in Excel720COUNTIF Text640Excel COUNTIF Cell Contains Text590Excel COUNTIF Multiple Conditions590Excel COUNTIF UNIQUE Values590COUNTIF Excel Color Cells480How to Apply COUNTIF Formula in Excel390Excel COUNTIF Two Criteria390COUNTIF Date Range390Use COUNTIF for a Coloum Dubblicat Function in Excel320Excel COUNTIF Two Conditions320COUNTIF Blank Cells in Excel260Excel COUNTIF Contains Text140Excel COUNTIF NOT Equal to Text140COUNTIF Words in Excel140How to Write a COUNTIF Formula in Excel70COUNTIF Formula for Checkbox in Excel50

Other sub-topics

Formulas · 454Cells · 431Percent · 57CAGR · 19COUNT · 52Subtract · 25Compound Interest · 21Multiplication · 11P Value · 12Show Formulas · 19Calculate Age · 18SUM · 34