In Excel, use COUNTIF to count cells that meet one condition. With statuses in A2:A10 and four cells containing Paid, enter =COUNTIF(A2:A10,"Paid"); the result is 4. The function can also count numbers, dates, blanks, and text matching a pattern.
What COUNTIF means and when to use it
COUNTIF combines “count” and “if”: it checks each cell in a range against a condition, then returns the number of cells that satisfy it. It returns a count, not the matching values and not the sum of numbers in those cells. For example, it can tell you how many orders are marked Paid, how many scores are at least 70, or how many dates fall after a cutoff.
It is useful when you need a quick count based on one rule. Common applications include tracking inventory below a reorder level, counting attendance marks, summarizing survey responses, and checking whether a category appears a certain number of times. The result updates when the source cells change, so it is more useful for a live summary than manually counting entries.
COUNTIF applies one condition to one range. If a question depends on two or more conditions at once—for example, counting Paid orders for Seattle—you will usually want COUNTIFS instead. That distinction matters because trying to combine multiple conditions inside a single COUNTIF does not make it a multi-condition formula.
How the COUNTIF formula works
The function has two inputs: the range of cells to inspect and the criterion to test. Put the range first and the criterion second. The criterion can be text, a number, a comparison such as “greater than 10,” or a wildcard pattern. In the opening example, Excel checks A2:A10 and counts every cell whose content matches Paid.
For a basic text count, with product categories in B2:B8 and three cells containing Books, use =COUNTIF(B2:B8,"Books"); the result is 3. Text criteria are entered inside straight double quotation marks. A criterion can also refer to a cell: with categories in B2:B8, the word Books in D2, and three matches in the range, enter =COUNTIF(B2:B8,D2); the result is 3. This makes it easy to change the value being counted without editing the formula.
For simple comparisons, put the comparison operator and value together inside quotation marks. Operators include greater than (>), less than (<), equal to (=), greater than or equal to (>=), and not equal to (<>). When the comparison value is in another cell, join the operator text to that cell reference with an ampersand; this lets the criterion change along with the value in the cell.
Count text with exact matches and wildcards
A criterion such as Paid counts cells with that text, even if the capitalization differs: COUNTIF is not case-sensitive. It treats Paid, PAID, and paid as matches. It does not, however, treat a longer entry such as Unpaid as an exact match for Paid. That is helpful for clean category counts, but it is worth remembering if the data includes similar words or inconsistent labels.
Wildcards let a criterion match a pattern rather than one complete entry. An asterisk (*) stands for any number of characters, including no characters; a question mark (?) stands for exactly one character. For example, with department names in C2:C7 containing North, Northwest, South, North Hub, N, and north, enter =COUNTIF(C2:C7,"North*"); the result is 4. The asterisk allows anything after North, and the case-insensitive comparison includes north.
To count text with a particular number of characters, use question marks. With codes in C2:C7 containing A12, B345, C678, D12, E987, and F1, enter =COUNTIF(C2:C7,"???"); the result is 4 because four entries have three characters. If the text you need to match actually contains an asterisk or question mark, put a tilde (~) before that character to treat it literally. This is uncommon in ordinary labels, but can matter when counting text that includes wildcard symbols.
Count numbers and values that meet a threshold
COUNTIF can compare numeric values as well as match them exactly. With scores in B2:B7 containing 8, 12, 15, 12, 19, and 7, enter =COUNTIF(B2:B7,">=12"); the result is 4 because 12, 15, 12, and 19 meet the condition. A comparison criterion such as ">=12" is text inside the formula, even though Excel evaluates it against numeric cell values.
For a changeable threshold, with the same scores in B2:B7 and the number 12 in D2, enter =COUNTIF(B2:B7,">="&D2); the result is 4. The ampersand joins the comparison operator to the value in D2 to create the criterion. If D2 changes to 15, the formula recalculates and returns 2. This is convenient for a small report where a user needs to choose a threshold.
Exact numeric counts work too. With scores in B2:B7 containing 8, 12, 15, 12, 19, and 7, enter =COUNTIF(B2:B7,12); the result is 2. If numbers were imported as text, results may not behave as expected because a text value such as “12” is not always equivalent to the number 12. Check the source data and convert values to actual numbers when appropriate.
Count dates and blank cells
Excel stores dates as serial numbers, so COUNTIF can compare dates using the same greater-than and less-than operators used for numbers. It is generally safer to build a date criterion with the DATE function than to type a date string whose interpretation might depend on regional date settings. With dates in E2:E6—January 3, January 15, February 2, December 31, 2024, and January 1, 2025—enter =COUNTIF(E2:E6,">="&DATE(2025,1,1)); the result is 3. The three dates on or after January 1, 2025 are counted.
To count cells that are genuinely empty, with entries in F2:F6 consisting of Ready, a blank cell, Hold, a blank cell, and Done, enter =COUNTIF(F2:F6,""); the result is 2. A blank criterion can also match a formula cell that displays an empty string, so a counted “blank” is not always a physically empty cell. If a workbook uses formulas to hide values, inspect those formulas before treating the count as a count of untouched cells.
To count filled cells rather than blank ones, with the same F2:F6 example containing three text entries and two genuinely empty cells, enter =COUNTIF(F2:F6,"<>"); the result is 3. This is useful for a simple completion count, though data quality matters: a cell containing a space is not empty and may be counted as filled.
When to use COUNTIFS instead
Choose COUNTIFS when every counted row must satisfy multiple conditions. For example, with cities in A2:A6 and order statuses in B2:B6 arranged as Seattle/Paid, Seattle/Open, Portland/Paid, Seattle/Paid, and Portland/Open, enter =COUNTIFS(A2:A6,"Seattle",B2:B6,"Paid"); the result is 2. Both conditions must be true on the same row for that row to contribute to the count.
COUNTIFS pairs each criteria range with a criterion, and it can evaluate several such pairs. The ranges should be the same size and shape; using mismatched ranges can produce an error or an unexpected result. Use COUNTIF for one condition and COUNTIFS for two or more conditions. If you need to count rows that meet either of two alternative conditions, the setup is different: separate COUNTIF calculations can sometimes be added, but overlapping categories can cause a row to be counted twice.
Common COUNTIF problems and how to fix them
- The result is zero unexpectedly: Check for leading or trailing spaces, spelling differences, and numbers stored as text. A cell that looks like Paid may contain an extra space that prevents an exact text match.
- The comparison criterion is malformed: Put a fixed comparison such as greater than 10 inside quotation marks. When the threshold is in a cell, join the quoted operator to the cell reference with an ampersand.
- The formula shows a syntax error: Check that the range and criterion are in the right order and that quotation marks and parentheses are balanced. Excel installations with regional settings that use semicolons may require semicolons rather than commas between arguments.
- Unexpected matches appear: Remember that COUNTIF ignores capitalization and that wildcards broaden a match. Replace a wildcard with an exact criterion if you want only one complete text value.
- The count changes when copied: Relative references such as A2:A10 shift when a formula is copied. Use dollar signs to lock a range when needed; for example, a range written as $A$2:$A$10 remains fixed when copied.
- A full-column count is slow in a large workbook: A reference such as A:A is convenient, but formulas that repeatedly scan full columns can add work in complex files. Use a realistic data range or an Excel Table when that better fits the workbook.
When building a summary, first check a few source rows manually, then compare the formula’s result with the expected count. If the count is part of a report, label the condition clearly—for example, “Paid orders”—so another person can tell what is being counted without reverse-engineering the formula.
Frequently Asked Questions
What is the difference between COUNT and COUNTIF?
COUNT counts cells containing numbers. COUNTIF counts cells that meet a condition, which can be a text match, a numeric test, a date comparison, or a wildcard pattern. Use COUNT when you simply need the number of numeric entries and COUNTIF when the entries must satisfy a rule.
Can COUNTIF count cells that contain a word anywhere?
Yes. Use an asterisk before and after the word to match text containing it. For example, with descriptions in A2:A20, a criterion of "*late*" matches entries with late anywhere in the text. The wildcard can also match other text around the word, so it may find more than one specific phrase.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. If capitalization must distinguish a match, COUNTIF alone is not the right tool; a case-sensitive approach needs a different formula design.
Can one COUNTIF formula test two conditions?
No. COUNTIF tests one condition. Use COUNTIFS for multiple conditions that must all be true, such as a matching city and status on the same row. For alternatives where either condition may be true, plan the calculation carefully to avoid counting overlapping rows twice.
Why does COUNTIF not count a value that looks identical?
The cells may differ in a way that is not obvious, such as an extra space, text stored instead of a number, or a date stored as text. Check the underlying values and formatting, and clean or convert the source data if needed. Formatting changes how a value looks; it does not necessarily change the value Excel evaluates.
Related
- Large Excel Cheat Sheet Mouse Pad: What to Know
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DATEDIF Spreadsheet Examples: Years, Months, and DaysIn Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years,…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- FV Examples in Excel: Formula and Worked ExamplesExcel’s FV function calculates the future value of money invested or saved at a steady interest rate. For…
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.