What is COUNTIF?
COUNTIF counts the number of cells in a range that meet a single condition. It is one of the most frequently used functions for data analysis in Excel and Google Sheets. Use it to count sales over a threshold, tally responses, track statuses, or summarize categorical data without manually scanning rows.
How to Use This Generator
- Go to the ExcelForm tool on the homepage
- Click the Generate tab (or the relevant tool tab for your task)
- Describe what you want to count in plain English — include the column, the condition, and any multiple criteria
- Select your environment (Excel, Google Sheets, or Both)
- Click Generate Formula and copy the result directly into your spreadsheet
Example: COUNTIF Formula Generator in Action
"Count how many orders in column B are over $100"
=COUNTIF(B:B,">100")
"Count how many sales in column A are East region and in column B are over 500"
=COUNTIFS(A:A,"East",B:B,">500")
Common Mistakes to Avoid
- Forgetting to wrap text criteria in double quotes — numeric criteria can be bare, but text must be quoted: "Completed" not Completed
- Using COUNTIF when you need COUNTIFS for multiple conditions — COUNTIF only handles one criteria pair
- Mismatched range sizes in COUNTIFS — every criteria range must have the same number of rows and columns
- Using comparison operators without quotes — combine them with the value inside quotes: ">100" not >100
COUNTIF vs COUNTIFS
COUNTIF evaluates a single condition against one range. COUNTIFS evaluates multiple conditions, each with its own range, and only counts rows where every condition is true. COUNTIFS is available in Excel 2007+ and all versions of Google Sheets. If you start with one condition and later need more, simply switch to COUNTIFS by adding additional range-criteria pairs.
COUNTIF With Dates, Text, and Wildcards
COUNTIF criteria can be numbers, text, dates, expressions, or wildcard patterns. For date comparisons, use the same formatting as your data or reference a date cell. For partial text matches, use the asterisk (*) for any sequence of characters and the question mark (?) for a single character.
To count blank cells, use "" as the criteria. To count non-blank cells, use "<>". If you need to count a literal asterisk or question mark, prefix it with a tilde: "~*" or "~?".
Related Functions
Excel offers several counting functions that complement COUNTIF. Knowing when to use each one saves time and keeps formulas clean.
- COUNT: Counts cells containing numbers only. Ignores text, blanks, and errors. Use for pure numeric columns.
- COUNTA: Counts all non-empty cells including text, numbers, and errors. Use when you need a total row count regardless of data type.
- COUNTBLANK: Counts empty cells in a range. Useful for data-quality checks and identifying missing responses.
Frequently Asked Questions
How do I generate a COUNTIF formula from plain English?▾
What is the difference between COUNTIF and COUNTIFS?▾
How do I use wildcards in COUNTIF?▾
=COUNTIF(A:A,"*Smith*") counts all cells containing Smith anywhere in the text.Why does my COUNTIF return 0?▾
How do I count blank or non-blank cells?▾
=COUNTIF(A:A,"") to count blank cells, and =COUNTIF(A:A,"<>") to count non-blank cells. In Google Sheets, COUNTBLANK(A:A) also works for empty cells.Is ExcelForm's COUNTIF generator free?▾
How do I count cells between two dates?▾
=COUNTIFS(A:A,">=1/1/2025",A:A,"<=3/31/2025"). For dynamic date ranges, reference cells containing the start and end dates.