COUNTIFS Function in Excel, Google Sheets, Airtable & Notion
COUNTIFS counts the rows where every condition is true, for example orders from the East region worth more than 100.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) |
| Google Sheets | ✔ Built in | COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
| 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. Add one condition per criteria pair. |
| Notion | ≈ Workaround | prop("Orders").filter(current.prop("Region") == "East" and current.prop("Amount") > 100).length()Combine the conditions inside one filter with and, then count with length(). |
| Excel | =COUNTIFS(A2:A7, "East", B2:B7, ">100") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =COUNTIFS(A2:A7, "East", B2:B7, ">100") | 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. Add one condition per criteria pair. | |
| Notion workaround | prop("Orders").filter(lower(current.prop("Region")) == "east" and current.prop("Amount") > 100).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.
Region Amount East 120 West 85 East 240 North 60 East 95 West 150
The Notion formula assumes a relation property Orders to a database with Region, Product and Amount properties. Text is compared in lower case to copy Excel's case-insensitive matching; wildcards become a test() regular expression.
Arguments
| Argument | What it means |
|---|---|
criteria_range1 | The first range to test. |
criteria1 | 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. |
[criteria_range2, criteria2], ... optional | More range/condition pairs. Every range must have the same size; a row counts only if it meets all conditions. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=COUNTIFS(A2:A7, "East", B2:B7, ">100") | 2 | Tester data: East orders of 120 and 240. | |
=COUNTIFS(A2:A7, "West", B2:B7, ">100") | 1 | Only the West order of 150. | |
=COUNTIFS(B2:B7, ">=60", B2:B7, "<=100") | 3 | The same range twice makes a between: 85, 60 and 95. | |
=COUNTIFS(C:C, ">="&TODAY()-7, D:D, "Open") | Open items from the last week | Date conditions joined to TODAY(). |
Examples use Excel / Google Sheets syntax unless noted.
How COUNTIFS works
COUNTIFS extends COUNTIF to several conditions. You give it pairs of a range and a condition, and it counts the rows where all of them are true. Using the same range twice gives a “between” count: =COUNTIFS(B:B, ">=60", B:B, "<=100"). The criteria rules are the same as COUNTIF: quoted operators, wildcards, case-insensitive text and ">"&E1 for cell comparisons.
All ranges must be the same size, or the result is #VALUE!. COUNTIFS always combines conditions with AND. For OR across values in one column, add COUNTIFS together or sum an array of criteria: =SUM(COUNTIFS(A:A, {"East","West"}, B:B, ">100")). For anything more complex, such as OR across different columns, SUMPRODUCT or FILTER with ROWS gives full control.
Airtable does this with a conditional rollup using COUNTALL(values), or a filtered view's record count. In Notion, put every condition in one filter() joined with and and take .length().
Frequently Asked Questions
- How do I count rows between two dates?
- Use the date column twice:
=COUNTIFS(D:D, ">="&G1, D:D, "<="&G2). - How do I count with OR conditions?
- Add COUNTIFS together, or use
=SUM(COUNTIFS(A:A, {"East","West"})). Do not double-count rows that match both.