COUNTIF Function in Excel, Google Sheets, Airtable & Notion
COUNTIF counts how many cells in a range meet a condition: how many orders are “Paid”, how many scores are above 80, how often a name appears.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | COUNTIF(range, criteria) |
| Google Sheets | ✔ Built in | COUNTIF(range, criterion) |
| Airtable | ✘ Not available | — Airtable formulas only see one record. Link the records to a summary record and add a rollup field with COUNTALL(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number. Within one record, count matches in a multi-value field with LEN({Tags}) - LEN(SUBSTITUTE({Tags}, "Paid", "")) divided by the length of the word. |
| Notion | ≈ Workaround | list.filter(current == "Paid").length()Filter the list (or relation) and take its length. == is case-sensitive; the copy-ready formula uses lower() to match COUNTIF. |
| Excel | =COUNTIF(A2:A6, "Paid") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =COUNTIF(A2:A6, "Paid") | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas only see one record. Link the records to a summary record and add a rollup field with COUNTALL(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number. Within one record, count matches in a multi-value field with LEN({Tags}) - LEN(SUBSTITUTE({Tags}, "Paid", "")) divided by the length of the word. | |
| Notion workaround | ["Paid", "Pending", "Paid", "Overdue", "Paid"].filter(lower(current) == "paid").length() | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Status Paid Pending Paid Overdue Paid
With a relation in Notion, write prop("Orders").filter(current.prop("Status") == "Paid").length().
Arguments
| Argument | What it means |
|---|---|
range | The cells to check. |
criteria | The condition, in quotes: "East", ">100", "<>Paid", "A*". A number or a cell reference also works. To use a cell in a comparison, join it: ">"&E1. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=COUNTIF(A2:A6, "Paid") | 3 | Tester data. “paid” or “PAID” would give the same result. | |
=COUNTIF(A2:A6, "<>Paid") | 2 | Everything that is not Paid (blank cells would count too). | |
=COUNTIF(A2:A6, "P*") | 4 | Wildcard: Paid three times plus Pending. | |
=COUNTIF(A:A, A2)>1 | TRUE if A2 is a duplicate | A common way to flag duplicates, e.g. in conditional formatting. |
Examples use Excel / Google Sheets syntax unless noted.
How COUNTIF works
COUNTIF takes a range and a single condition and returns how many cells match. The condition can be a value ("Paid", 42), a comparison in quotes (">=80", "<>Done") or a pattern with wildcards ("*error*" for “contains error”). Text matching ignores case. To compare with a cell, join the operator and the reference: ">"&E1.
Some useful patterns: "*" counts cells containing any text, "" counts blanks, "<>" counts non-blanks, and =COUNTIF(A:A, A2)>1 flags duplicates. Watch out for long numbers such as card or product IDs: COUNTIF converts text that looks like a number into a number, so IDs longer than 15 digits can match each other. Numbers stored as text and real numbers also match each other in COUNTIF, which is not true for most other functions. For several conditions use COUNTIFS.
Airtable formulas cannot count other records, so use a rollup with COUNTALL(values) over linked records and add conditions to the rollup, or simply filter a view and read the record count. In Notion, filter a list or relation and take .length(); Notion's == is case-sensitive, so wrap the value in lower() when case should not matter.