Set the range and the condition, and the COUNTIF formula builds as you type. Add a second condition and it becomes COUNTIFS. Operators, dates, wildcards and cell references get their quotes and & signs written for you.
Your formula appears here, e.g. =COUNTIF($A$2:$A$100,"East")
Nothing is uploaded. The formula is built in your browser as you type.
A COUNTIF generator turns a range, an operator and a value into a working COUNTIF formula, and into COUNTIFS once you add a second test. It answers "how many" questions: how many support tickets are still open, how many people on a 400-row signup sheet left an email address, whether an invoice number turns up twice. The signup one is a single condition, "is not blank", on the email column:
=COUNTIF($C$2:$C$401,"<>")Short, and still easy to get wrong. Blank has its own notation, a threshold in another cell needs &, and COUNTIF quietly treats the code 0123 as the number 123.
A2:A100. Then choose one of the eleven operators, which run from equals to is not blank.F1, or a date in year-month-day form (2026-01-31). Is blank and is not blank need no value.All of this runs on the page in your browser. The generator needs the range address, never the workbook, so there is nothing to upload.
We checked the generator's formulas in Excel for Mac against a five-row list: East, West, Eastern, an empty cell, and east in lower case.
| Condition | Criterion written | Count | Why |
|---|---|---|---|
equals East | "East" | 2 | Matches East and east. Case is ignored, and Eastern is a different cell value. |
contains ast | "*ast*" | 3 | East, Eastern and east all contain "ast". |
| is not blank | "<>" | 4 | Every cell except the empty one. |
| is blank | "" | 1 | Only the empty cell. |
On a column of 5, 15, 25, 35 and 45, "is at least F1" with 20 in F1 wrote ">="&F1 and counted 3. A two-condition COUNTIFS, begins with East and is greater than 10, counted 2.
Two of those results catch people out. Equals means the entire cell, so "East" will never pick up "Eastern" or "East Region"; use begins with or contains for that. And no criterion can tell East from east. Capitals don't matter to COUNTIF.
Contains, begins with and ends with all turn into wildcards, and wildcards only match text. Counting cells that contain 5 in a column of numbers returns 0, even when the column holds 150 and 250.
"<>" counts any cell that holds something. That includes a formula returning an empty string, such as =IF(C2>0,"Paid","") on a row where C2 is 0. The cell shows nothing, but COUNTIF counts it as not blank, and "is blank" ("") counts it too. On a column of formulas, the two counts can overlap.
When the column is formulas and you want only the cells that show something, count by length instead:
=SUMPRODUCT(--(LEN($A$2:$A$100)>0))For plain typed data, COUNTA and COUNTBLANK give the same totals as "<>" and "" with no criterion to write.
COUNTIF converts text that looks like a number into that number before it compares. A part code stored as text 0123, another stored as 00123, and the number 123 all count as one value. That's fine for quantities. It breaks when leading zeros are part of an ID.
Long IDs hit a harder wall. Excel keeps 15 significant digits, and COUNTIF turns a 16-digit card number stored as text back into a number, so two card numbers that differ only in the last digit count as the same. The usual symptom is a duplicate check flagging rows that aren't duplicates.
The standard duplicate check is itself a COUNTIF. Pick equals, type A2 as the value, and the generator writes:
=COUNTIF($A$2:$A$100,A2)The range gets $ signs; the A2 you typed goes in as you typed it, so filling the formula down checks A3, A4 and so on against the whole list. Add >1 in the cell to get TRUE on repeats. For IDs longer than 15 digits, compare the cells directly instead, which keeps every digit:
=SUMPRODUCT(--($A$2:$A$100=A2))To count each value once rather than flag repeats, see counting unique values.
COUNTIF takes a range and a criterion. COUNTIFS takes that pair, then another, then another. When you add a condition, the generator appends the new pair at the end and shows a note that the order hasn't changed. People coming from SUMIFS expect a reshuffle here, because SUMIFS moves its sum range to the front.
Picture a helpdesk export: regions down column A, the date each ticket was opened down column B. "How many East tickets since 1 January" is one pair on each column:
=COUNTIFS($A$2:$A$100,"East",$B$2:$B$100,">="&DATE(2026,1,1))The date went in as 2026-01-01, so the generator wrote DATE(2026,1,1). Typed as 1/1/2026 it would be read with each computer's own date setting, and the page warns you when you do that.
A row counts only when all of its conditions hold. For a window of dates that is exactly right: use the date column twice, "is at least" 2026-01-01 and "is less than" 2026-02-01, and you get January, including a ticket stamped at 16:45 on the 31st.
=COUNTIFS($B$2:$B$100,">="&DATE(2026,1,1),$B$2:$B$100,"<"&DATE(2026,2,1))For either-or on one column, the same rule works against you. East and West on the region column gives 0, since a cell holds one region. The generator warns that COUNTIFS is AND only and points you to two COUNTIFs added together.
Every extra range is read against the first one, row by row. If a pair doesn't line up, the generator tells you why instead of writing the formula: a range with a different number of cells gives #VALUE! in Excel, and one starting a row lower matches each region with the next ticket's date, so the count is wrong and nothing flags it.
In Excel 365, Formulas > More Functions > Statistical > COUNTIF opens a two-box dialog, Range and Criteria, and shows the result as you fill it in. The Criteria box wants the same quotes and ampersands the generator writes. For a one-word condition you know by heart, =COUNTIF(A:A,"Closed") typed into the cell is faster than any dialog.
When the question is "how many of each", skip the column of COUNTIFs. A pivot table with Status dragged into Rows and again into Values lists every status with its count. To count what a filter is showing, =SUBTOTAL(103,A2:A100) counts the non-empty visible cells and ignores the hidden ones.
SUM(COUNTIFS(...,{"East","West"})) yourself.Worked examples for each argument are on the COUNTIF and COUNTIFS function pages. If the next question is "how much" rather than "how many", the SUMIF generator writes its criteria the same way and adds up the matching rows instead.
COUNTIF counts cells that meet one condition, and COUNTIFS counts rows that meet every one of several conditions. The arguments come in the same order in both: a range, then its condition, repeated for each extra pair in COUNTIFS. With a single condition the two return the same number.
Use "<>" as the criterion: =COUNTIF(A2:A100,"<>"). It counts every cell that holds something, including a formula that returns an empty string, so a column of IF formulas can look half empty and still count as full. For that case, =SUMPRODUCT(--(LEN(A2:A100)>0)) counts only cells that show a value.
Put asterisks around the text: =COUNTIF(A2:A100,"*urgent*") counts every cell with urgent anywhere in it. For text held in a cell, join the asterisks on with ampersands, as in "*"&F2&"*". Wildcards only match text, so "*5*" will not count the number 150.
No, COUNTIF and COUNTIFS ignore case, so "East" counts East, EAST and east alike. For a case-sensitive count, use =SUMPRODUCT(--EXACT(A2:A100,"East")), which compares each cell letter for letter.
Add one COUNTIF per value: =COUNTIF(A2:A100,"East")+COUNTIF(A2:A100,"West"). COUNTIFS with East and West on the same column returns 0, because no cell can be both. For a longer list, =SUM(COUNTIFS(A2:A100,{"East","West","North"})) does the same in one formula.
The usual causes are a cell reference inside the quotes (">F1" instead of ">"&F1), extra spaces in the data, or codes that look like numbers. COUNTIF reads 0123 and 123 as the same value, and treats IDs longer than 15 digits as equal once the first 15 digits match. Formulas that return an empty string also count as not blank.
Yes, COUNTIF and COUNTIFS count every matching cell whether the row is visible or not. To count the non-empty cells a filter leaves on screen, use =SUBTOTAL(103,A2:A100), then set the condition with the filter itself.