To count cells that meet one condition in Microsoft Excel, use =COUNTIF(range,criteria). For example, with status values in B2:B11, =COUNTIF(B2:B11,"Paid") counts cells containing Paid. You need an Excel workbook with the data you want to check; the formula works in Microsoft 365 for Windows, Mac, and Excel for the web.
What You’ll Need
- An Excel workbook containing the values you want to count.
- A clear idea of the range to check and the condition to apply, such as a particular word, a number threshold, or a date.
- A blank cell for the result. The formula can go on the same worksheet or another worksheet in the workbook.
You do not need to enable an add-in or turn on a special setting. COUNTIF is a built-in worksheet function. In some regions, Excel expects semicolons instead of commas between formula arguments because of local list-separator settings; use the separator Excel accepts in your installation.
Step-by-Step
- Identify the cells to check. Open the worksheet and find the column or row containing the information you want to count. For example, a task list might have status values in column B, dates in column A, and task names in column C. Select the whole data range you mean to include, but avoid selecting unrelated headings if you do not want them considered. A range such as B2:B11 includes cells B2 through B11.
- Choose the condition. Decide what should qualify as a match. A condition can be exact text such as Paid, a number such as 100, a comparison such as 100 or more, or a pattern such as any text beginning with North. COUNTIF checks one range against one condition at a time. If the worksheet has several different conditions to count, you can create separate COUNTIF formulas or use COUNTIFS for a calculation that requires multiple conditions at once.
- Select a result cell and enter the basic formula. Click an empty cell where the count should appear. Type an equals sign, the function name, an opening parenthesis, the range, a comma, the condition, and a closing parenthesis. Excel may show a function suggestion while you type; choose COUNTIF if the suggestion list appears. With status values in B2:B11, where exactly five cells contain Paid and the rest contain other statuses, enter
=COUNTIF(B2:B11,"Paid"). The result is 5. Text criteria go inside straight double quotation marks. - Count a number or use a comparison. For a numeric threshold, put the comparison operator and number together inside quotation marks. With the values 45, 100, 125, 99, 240, 75, 100, 18, and 130 in C2:C10, enter
=COUNTIF(C2:C10,">=100"). The result is 5, because 100, 125, 240, 100, and 130 are at least 100. Common operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). - Use a cell as the condition when it may change. Instead of typing a fixed threshold into the formula, keep the threshold in a cell and join the comparison operator to that cell reference with an ampersand. With the same values in C2:C10 and the threshold 100 in E2, enter
=COUNTIF(C2:C10,">"&E2). The result is 3, counting only 125, 240, and 130. If you change E2, Excel updates the count automatically. This approach is useful for reports where someone may adjust the target without editing the formula. - Count text that follows a pattern. COUNTIF supports wildcards in text criteria. An asterisk (*) stands for any number of characters, while a question mark (?) stands for exactly one character. With these names in A2:A6—North Shop, North Office, South Shop, North, and Northerly—enter
=COUNTIF(A2:A6,"North*"). The result is 4 because four entries begin with North. A criterion of"North?"would match North followed by one character, not any length of text. To search for a literal asterisk or question mark, put a tilde (~) before the character. - Count dates with a date-based criterion. Excel stores dates as numbers, so a comparison against a date can be built with DATE. With dates in A2:A8—December 15, 2024; January 1, 2025; January 10, 2025; February 3, 2025; December 31, 2024; March 1, 2025; and November 30, 2024—enter
=COUNTIF(A2:A8,">="&DATE(2025,1,1)). The result is 4, counting the dates from January 1, 2025 onward. DATE avoids ambiguity between date formats such as month/day/year and day/month/year. Make sure the cells contain real Excel dates rather than date-looking text. - Confirm and check the result. Press Enter in Excel for Windows or Return on a Mac. Excel for the web also accepts the formula in the selected cell. Check that the answer is a number and that the formula refers to the intended range. If you plan to copy the formula, consider whether the range should move with the formula or stay fixed. Select the range references in the formula bar and press F4 on Windows to cycle through reference types; on a Mac, the equivalent key can depend on keyboard settings, and you can type dollar signs directly, as in $B$2:$B$11.
The function has two arguments: range is the group of cells Excel checks, and criteria is what each cell must satisfy to be counted. The condition can be written directly in the formula or stored in another cell. COUNTIF returns one total, not a list of matching cells, so use a filter or another function if you need to display the matches themselves.
Common Mistakes
- Leaving out quotation marks around text. Text criteria such as Paid need straight double quotation marks. Curly typographic quotation marks copied from a word processor may not work as formula delimiters.
- Putting a comparison in the wrong form. For a numeric threshold written directly, enter the operator and value together as a quoted criterion, such as
">=100". When the threshold is in a cell, join the quoted operator to the cell reference with&; do not put the cell address inside the quotation marks. - Selecting mismatched or incomplete ranges. Check that the range includes all relevant rows and does not accidentally include totals or headings. COUNTIF only evaluates the range you give it.
- Expecting COUNTIF to distinguish uppercase and lowercase. COUNTIF text matching is not case-sensitive. It treats Paid and PAID as the same criterion. If capitalization must matter, COUNTIF alone is not the right tool.
- Counting dates stored as text. A cell may look like a date but contain text instead of an Excel date value. Comparisons may then return an unexpected total. Convert the entries to real dates before relying on a date criterion.
- Using the wrong argument separator. If Excel flags the formula as invalid, your regional settings may require semicolons instead of commas. Follow the separator Excel uses when it inserts functions in your version.
Tips
- Keep criteria in a separate cell when you expect to change them. This makes a worksheet easier to update and helps other people see the rule being applied.
- Use a specific range rather than an entire column when the worksheet is large or when headings and notes could be included by mistake. If you do use a whole-column reference, verify that no unrelated cells can match the condition.
- For a criterion that must match a complete text value, use the word itself rather than adding wildcards. The wildcard
*broadens a search, so"Paid*"can also match values such as Paid late. - Use COUNTIF to count blank cells by using an empty-string criterion. It can also count formulas that display an empty string, which may differ from cells that are truly empty. Check the source data if the blank count seems too high.
- If your rule needs more than one condition, consider COUNTIFS. For example, counting rows where a status is Paid and a region is East requires checking two ranges together; COUNTIF only accepts one condition range and one criterion.
- When copying a formula down a column, inspect the references in the first copied result. Relative ranges shift as the formula moves; absolute references marked with dollar signs stay fixed.
Troubleshooting
The formula shows an error as soon as you enter it. Check that the parentheses are balanced, text criteria use straight quotation marks, and the formula begins with an equals sign. If Excel rejects commas, replace the argument separators with the semicolons required by your regional settings. Also check for an accidental space or misspelling in the function name.
The result is zero even though matching values appear on screen. Compare the underlying contents, not just the display. A value may have a leading or trailing space, use a different spelling, or be stored as text while the criterion is numeric. Try clicking a sample cell and inspecting its contents in the formula bar. If imported text contains extra spaces, clean the source data or use a helper column to standardize it before counting.
The count is higher than expected. Look for wildcard characters in the criterion, duplicate entries, or additional matching cells beyond the visible part of the table. A criterion containing an asterisk intentionally matches a range of text values. Also check whether the selected range includes a heading, notes, or old data below the current list.
A date formula misses dates that look correct. Confirm that the cells are actual dates and not text. Check for hidden time values too: a cell displayed as January 1 may contain a time as well. If you are counting all entries on one date and the data includes times, an equality test may omit values later in that day; use a date range from the start of that day up to, but not including, the next day instead.
The result does not update after editing data. Press Enter after changing the source value and make sure calculation is not set to manual. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic if appropriate. Excel for Mac has calculation options on the Formulas tab as well; Excel for the web generally recalculates automatically. You can also select the formula cell and press F2, then Enter, to make Excel re-enter the formula.
Frequently Asked Questions
What is the difference between COUNT and COUNTIF?
COUNT counts cells containing numbers. COUNTIF counts cells in a range that meet a condition, which may be text, a number comparison, a wildcard pattern, or another supported criterion.
Can COUNTIF count more than one condition?
One COUNTIF formula applies one criterion to one range. Use COUNTIFS when a count must satisfy multiple conditions, such as a particular status and a particular region at the same time.
Does COUNTIF work in Excel for Mac and Excel for the web?
Yes. COUNTIF is available in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The formula is generally the same; local settings may change whether arguments are separated with commas or semicolons.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. If the distinction matters, you will need a case-sensitive approach rather than a standard COUNTIF criterion.
Why does COUNTIF return zero for a word I can see in the cell?
The cell may contain extra spaces, a different spelling, or hidden characters, or the formula may be checking the wrong range. Inspect the cell contents and verify the range and criterion. If you need a partial match, use a wildcard such as an asterisk where appropriate.
Can I use COUNTIF with a cell reference as its criterion?
Yes. For a simple exact match, refer to the cell containing the text or number. For a comparison, join the quoted operator to the reference with an ampersand, such as ">"&E2, so Excel can evaluate the value in E2 as part of the condition.
Related
- Find Duplicate Entries in an Excel Column
- How to Find Duplicates in Excel and Review Them Safely
- How to Find Duplicate Lines in Excel Without Deleting Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
- DAX Formula in Excel to Remove Duplicates: What WorksThere is no DAX formula in an Excel worksheet that deletes duplicate rows. For a live list of…
- Excel COUNTIF: Count Cells That Contain TextThe fastest way to count cells that contain any text in Excel is =COUNTIF(range,"*"), which counts every cell…
- Excel COUNTIFS Year Equals: How to Count by YearThe direct answer is to use the COUNTIFS function with a date range and a condition that checks…
- Match Excel Values: Exact Matches, Lookups, and ListsTo find an exact match in Excel, use MATCH with match type 0. If the value to find…
- Excel VLOOKUP Yes or No: How to Return a Match ResultYes. In Excel for Microsoft 365, you can make VLOOKUP return “Yes” or “No” by wrapping an exact-match…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.