COUNTIF in Excel: Examples and How to Use It Step by Step

To count cells that match one condition in Excel, use =COUNTIF(A2:A11,"Apple"). With fruit names in A2:A11, this returns the number of cells containing Apple. COUNTIF is useful for summarizing lists, checking whether values appear, and counting entries above or below a threshold. The examples below use Microsoft Excel for Microsoft 365 on Windows; the function works similarly in current Mac and web versions.

What You’ll Need

An Excel workbook with a range of data to count. The examples use small sample lists that you can type into a blank worksheet.
A spare cell for the result, such as E2, so the formula does not overwrite the source data.
For criteria based on a date or a changing number, a cell containing that date or number is helpful but not essential.

Step-by-Step

Set up a simple list. In a blank worksheet, enter these ten fruit names in A2:A11, one per row: Apple, Banana, Apple, Orange, Banana, Apple, Pear, Orange, Apple, Banana. Put a heading such as Fruit in A1. Keeping a heading outside the range makes it easier to select the data without accidentally including labels. You can use your own list instead, but make a note of the cells that contain the values you want to count.
Count an exact text value. Select an empty cell, such as E2, and enter the formula below. With fruit names in A2:A11, enter =COUNTIF(A2:A11,"Apple"). The result is 4 because Apple appears in four cells. Press Enter to calculate it. COUNTIF ignores letter case, so apple and APPLE match Apple too; it does not require the capitalization in the criterion to match the entries.

The first argument is the range Excel checks, and the second is the criterion it tests against each cell. Keep the range and criterion inside the parentheses, separated by a comma. If your Excel uses a regional setting that requires semicolons between formula arguments, use the separator Excel expects; the examples here show standard comma syntax.
Use a cell as the criterion. A cell reference makes the count easy to change without editing the formula. Type Apple into D2. With fruit names in A2:A11 and the requested fruit in D2, enter =COUNTIF(A2:A11,D2) in E2. The result is 4. Replace Apple in D2 with Banana and the displayed count updates to 3. This approach is useful for a search box, a summary table, or a report where people need to choose which item to count.

Do not put quotation marks around a cell reference. Excel should read D2 as the address of a cell, not as literal text. By contrast, a literal word typed directly into a formula needs quotation marks, as in the Apple example.
Count numbers using a comparison. Put these numbers in B2:B11: 12, 8, 15, 6, 12, 20, 9, 12, 4, 15. With those values in B2:B11, enter =COUNTIF(B2:B11,">10"). The result is 6: the values 12, 15, 12, 20, 12, and 15 are greater than 10. The comparison operator belongs inside the quoted criterion. You can use operators such as >, <, >=, <=, and <> for greater than, less than, inclusive comparisons, and not equal to.

To make the cutoff adjustable, put 10 in D3. With the numbers in B2:B11 and the cutoff in D3, enter =COUNTIF(B2:B11,">"&D3). The result is again 6. The ampersand joins the comparison text to the value in D3. If you change D3 to 12, the formula counts numbers greater than 12 instead.
Find text that starts or ends with certain characters. COUNTIF supports wildcards in text criteria. An asterisk represents any number of characters, including none; a question mark represents one character. Enter North, Northeast, North-East, Northwest, South, East, and North in C2:C8. With those region names in C2:C8, enter =COUNTIF(C2:C8,"North*"). The result is 5 because five entries begin with North. A criterion such as *East counts entries ending in East, while a criterion such as N?rth uses the question mark for exactly one character between N and rth.

If you need to search for an actual asterisk or question mark in the cell text, put a tilde before that wildcard character in the criterion. For example, ~* asks Excel to match a literal asterisk. This distinction matters when the data contains symbols rather than wildcard patterns.
Count dates from a particular day onward. Dates must be stored as real Excel dates, not text that only looks like a date. Enter these dates in D2:D8: January 5, 2026; January 12, 2026; February 2, 2026; February 16, 2026; March 1, 2026; March 12, 2026; and December 31, 2025. With those dates in D2:D8, enter =COUNTIF(D2:D8,">="&DATE(2026,2,1)). The result is 4: February 2, February 16, March 1, and March 12 are on or after February 1, 2026. DATE builds a date value reliably, and joining it to the comparison operator gives COUNTIF a date criterion.

To count dates before a boundary, change the comparison to <. For a period with both a start and end date, use COUNTIFS instead, because that task has two conditions. Make sure the boundary dates and the dates in the range use the same intended year and are genuine date values.
Handle a count that needs two conditions. COUNTIF applies one condition to one range. For example, to count rows where a region is North and a status is Open, use COUNTIFS, which can test multiple paired ranges. Put North, South, North, and North in A2:A5, and Open, Open, Closed, and Open in B2:B5. With regions in A2:A5 and statuses in B2:B5, enter =COUNTIFS(A2:A5,"North",B2:B5,"Open"). The result is 2 because two rows meet both conditions. Keep each criteria range the same size as the other criteria ranges so the row-by-row matches line up.
Check the range and copy the formula carefully. Confirm that the range starts and ends on the intended data rows, then compare the returned count with a quick visual check. If you copy a formula down a column, relative references such as A2:A11 may shift. To keep the source range fixed, use dollar signs: for example, with fruit names in A2:A11, =COUNTIF($A$2:$A$11,D2) keeps the list fixed while the criterion reference can change by row. The dollar signs are especially useful when building a summary table of counts for several categories.

Common Mistakes

Including the heading in the range: A text heading usually will not affect a count for a specific fruit, but it can affect criteria such as counting nonblank cells. Select only the data rows, or use a whole-column reference when that suits the sheet and performance is not a concern.

Leaving comparison operators outside the quotes: For a fixed numeric threshold, the criterion should be written as text such as “>10”. Writing the operator in the wrong place may produce an error or an unintended match. If the threshold is stored in a cell, join the operator and reference with &.

Using the wrong number of criteria: COUNTIF cannot test one condition in one column and a second condition in another. Use COUNTIFS for multiple conditions. Also check that each COUNTIFS criteria range covers the same number of rows.

Expecting case-sensitive matching: COUNTIF is not case-sensitive. It treats Apple and APPLE as equal for this purpose. If capitalization itself must distinguish entries, COUNTIF alone is not the right tool.

Typing dates as text: A date that Excel stored as text may not compare as expected with a real date. Check the cell format and, where necessary, convert the source values to actual dates before counting.

Tips

Use a cell reference for criteria that may change. It makes a worksheet easier to maintain and lets someone select a new category or threshold without touching the formula. For comparison criteria, remember that the operator must be joined to the reference, as in the numeric example.

Use wildcards for flexible text searches, but be deliberate about what they match. A criterion ending in an asterisk can include longer names that share the same beginning. For instance, North* includes North-East as well as North. Review the possible values in the source list before relying on a prefix match.

When building a report, place the source data in a consistent column and keep summary formulas outside that data. If the list will grow, consider converting it to an Excel table using Insert > Table. Tables expand when you add rows, although a formula written with ordinary cell references does not automatically become a structured-reference formula unless you enter the table column reference yourself.

Use COUNTIF to check list quality as well as to create summaries. You can count a repeated ID, a particular status, or values that meet a threshold. For more than one condition, switch to COUNTIFS rather than trying to combine separate tests inside COUNTIF.

Troubleshooting

The result is zero, but matching items are visible: Check for extra spaces, spelling differences, or a range that misses the relevant rows. A criterion referring to D2 also depends on D2 containing the same text as the data. COUNTIF ignores case, but it still distinguishes different characters and extra spaces. If data was imported, clean or standardize it before counting.

The formula displays instead of calculating: Make sure the entry begins with an equals sign and that the cell is not formatted as Text. Change the cell format to General, then edit the formula and press Enter again. Also check that Show Formulas is not enabled; press Ctrl+` on Windows to toggle it. On Mac, use Control+` if that key is available, or turn off Show Formulas from the Formulas tab.

Excel reports a formula error: Check that the parentheses are balanced, the quotation marks are straight double quotes, and the arguments are separated with the list separator your Excel installation expects. In some regional configurations, Excel uses semicolons rather than commas. If the error is #NAME?, check for misspelling in COUNTIF and confirm that text criteria are enclosed in quotes.

A date or number count seems wrong: Verify that values are numeric or real dates rather than text. Select a sample cell and inspect the formula bar or change its number format to see how Excel interprets it. For dates, make sure the year and month/day order are correct for your regional settings. For thresholds, check that you chose the intended operator, such as > rather than >=.

Frequently Asked Questions

Can COUNTIF count cells that are not blank? Yes. Use the not-equal operator with an empty string as the criterion. With the values to check in A2:A20, enter =COUNTIF(A2:A20,"<>"). The result is the number of nonblank cells in that range. Cells containing a formula that returns an empty string may behave differently from truly empty cells, so check your data if the total surprises you.

Does COUNTIF distinguish uppercase and lowercase? No. COUNTIF treats text matching without regard to case, so Apple and APPLE count as the same text. If case-sensitive counting is essential, use a different approach rather than assuming a capitalization change will affect COUNTIF.

Can COUNTIF count partial text? Yes. Use an asterisk for any number of characters or a question mark for one character. For example, a criterion like *berry matches text ending in berry, while berry* matches text starting with berry. Check whether the wildcard pattern could also match values you do not intend to include.

What is the difference between COUNTIF and COUNTIFS? COUNTIF checks one condition. COUNTIFS checks multiple conditions, such as a region and a status on the same row. Use COUNTIFS when every condition must be met; its criteria ranges should have matching dimensions.

Why does my formula use semicolons instead of commas? Excel uses the list separator set by regional settings. If Excel rejects a comma-separated formula, try the separator it uses in other formulas, often a semicolon. The function name and counting logic remain the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.