To use COUNTIF in Excel, select an empty cell, type =COUNTIF(A2:A10,"Paid"), and press Enter on Windows or the web, or Return on Mac. Replace A2:A10 with the cells you want to count and "Paid" with your condition. Excel returns the number of cells that match.
COUNTIF handles one condition, such as matching text, numbers above a limit, or dates after a cutoff. You can enter it directly in Excel for Microsoft 365, Excel 2024, or Excel for the web without an add-in or download.
The short version
- Basic formula: Use
=COUNTIF(range,criteria)to count cells that meet one condition. - Quotation marks: Put text and comparison expressions in double quotation marks, but leave cell references unquoted.
- Reusable counts: Reference a cell containing your condition and lock the counting range before filling the formula down.
- Multiple conditions: Use COUNTIFS when every condition must be satisfied.
| What you want to count | Formula | What matches |
|---|---|---|
| Exact text | =COUNTIF(A2:A10,"Paid") | Cells matching Paid, regardless of case |
| A value in another cell | =COUNTIF(A2:A10,E2) | Cells matching E2 |
| Text beginning with a word | =COUNTIF(A2:A10,"Paid*") | Text starting with Paid |
| Text containing a word | =COUNTIF(A2:A10,"*Paid*") | Text containing Paid anywhere |
| A literal asterisk | =COUNTIF(A2:A10,"~*") | Cells containing only an asterisk |
| An exact number | =COUNTIF(B2:B10,100) | Values equal to 100 |
| Numbers above a limit | =COUNTIF(B2:B10,">100") | Values greater than 100 |
| Numbers at or below a limit | =COUNTIF(B2:B10,"<=100") | Values at most 100 |
| Values unequal to another cell | =COUNTIF(B2:B10,"<>"&E2) | Values unequal to E2 |
| Dates after a cutoff | =COUNTIF(B2:B10,">10/1/2026") | Dates after October 1, 2026, with US date settings |
| Either of two text values | =COUNTIF(A2:A10,"Paid")+COUNTIF(A2:A10,"Pending") | Paid or Pending |
| Rows meeting two conditions | =COUNTIFS(A2:A10,"Paid",B2:B10,"East") | Paid in column A and East in column B |
| Numbers within an interval | =COUNTIFS(B2:B10,">=100",B2:B10,"<=200") | Values from 100 through 200 inclusive |
Enter your first COUNTIF formula
Select an empty result cell outside the range you are counting. Type the following formula into that cell or the formula bar, then press Enter on Windows or the web, or Return on Mac.
=COUNTIF(A2:A10,"Paid")The first argument, A2:A10, tells Excel which cells to examine. The second argument, "Paid", tells it what to match. Keep the comma between the arguments and the closing parenthesis at the end.
If three cells in that range contain Paid, the result is 3. COUNTIF ignores letter case, so Paid, PAID, and paid all match this condition.
For help entering the arguments on Windows, select your result cell, then select Insert Function on the formula bar. Enter COUNTIF in Search for a function, select Go, and double-click COUNTIF to open Function Arguments. Enter your cells in Range and your condition in Criteria, then select OK.
On Mac, select your result cell, then select Formulas > Insert Function to open Formula Builder. Search for COUNTIF, double-click COUNTIF, supply the range and criterion, and press Return.
Count text or use a condition from another cell
Put text directly in the formula inside double quotation marks, as in "Paid". For a condition you want to change often, enter the value in a separate cell instead. If E2 contains Paid, enter this formula:
=COUNTIF(A2:A10,E2)Leave E2 outside quotation marks so Excel uses the cell's value. Changing E2 lets you count a different value without editing the formula.
Wildcards let you match part of a cell's text. Use the following formula to count text beginning with Paid:
=COUNTIF(A2:A10,"Paid*")Change the criterion to "*Paid*" to count text containing Paid anywhere. An asterisk matches any sequence of characters, while a question mark matches one character. To count cells containing only a literal asterisk, use "~*" as the criterion.
Count numbers and dates
A numeric equality condition does not need quotation marks, so =COUNTIF(B2:B10,100) counts values equal to 100. For comparisons, put the operator and number together inside quotation marks. This formula counts values greater than 100:
=COUNTIF(B2:B10,">100")Use "<=100" to count values at most 100. When the comparison value is stored in another cell, join the operator to that reference with an ampersand. For example, this counts values unequal to E2:
=COUNTIF(B2:B10,"<>"&E2)Dates can also be compared, provided the range contains actual Excel dates. With US date settings, the following example counts dates after October 1, 2026:
=COUNTIF(B2:B10,">10/1/2026")Typed date strings depend on your regional settings, so use the date order Excel expects. For an inclusive interval with both a starting and ending date, use COUNTIFS with a lower and an upper condition.
Combine conditions correctly
To count either of two distinct text values, add two COUNTIF results. This example counts cells containing Paid plus cells containing Pending:
=COUNTIF(A2:A10,"Paid")+COUNTIF(A2:A10,"Pending")These two exact text conditions cannot match the same cell. Other combinations can overlap, so check that your conditions do not count the same cell twice.
Use COUNTIFS when a row must meet every condition. If column A contains statuses and column B contains regions, this formula counts rows with Paid in column A and East in column B:
=COUNTIFS(A2:A10,"Paid",B2:B10,"East")Keep the paired ranges the same size. You can also apply two conditions to the same range, such as counting numbers from 100 through 200 inclusive:
=COUNTIFS(B2:B10,">=100",B2:B10,"<=200")Copy COUNTIF into a summary
List the values you want to count in E2 and the cells below it. In F2, enter the following formula, then drag F2's fill handle down beside the remaining values:
=COUNTIF($A$2:$A$100,E2)The dollar signs keep the counting range fixed as you fill down. The criterion reference changes from E2 to E3, E4, and so on, giving each listed value its own count. Without a fixed range, copying the formula can move the cells being counted.
Check that each copied formula still refers to the full counting range and the criterion beside it. Once the counts are ready, you can make a graph in Excel to compare the totals.
Use a table for a growing list
An Excel table column can replace an ordinary range in COUNTIF. For an existing table named Table1 with a Status column, enter this formula in a cell outside the table:
=COUNTIF(Table1[Status],"Paid")Structured references adjust when table rows are added or removed. When you insert a row in Excel, make sure the new entry belongs to the table so the table reference includes it.
To create a table from headed data on Windows, select the data and press Ctrl + T. Check My table has headers, then select OK. Use your table's actual name and column heading in the formula.
Find repeated values with COUNTIF
COUNTIF can show how often each value occurs in a list. Enter this formula beside A2, then fill it down beside the remaining entries:
=COUNTIF($A$2:$A$100,A2)A result greater than 1 means the value appears more than once in the range. Review those repeated entries before you remove duplicates in Excel. The formula counts occurrences without removing anything.
Fix formula errors and unexpected results
For a count that looks wrong, first confirm that the range covers the intended cells and the criterion expresses your condition. Text and comparison expressions need double quotation marks, while a criterion taken directly from another cell does not. Remember that COUNTIF treats uppercase and lowercase text as matches.
If Excel rejects the commas in a formula, use the separator required by your regional settings. A common alternative is a semicolon:
=COUNTIF(A2:A10;"Paid")On Windows, if a cell displays the formula literally, open Format Cells and select General. Then press F2, followed by Enter, to enter the formula again. If the whole sheet is displaying formulas, toggle Formulas > Show Formulas.
For a result that does not update on Windows, select Formulas > Calculation Options > Automatic. For an immediate recalculation, select Formulas > Calculate Now or press F9. Desktop calculation settings affect other open workbooks too.
A COUNTIF formula referring to another workbook can return #VALUE! when that source workbook is closed. Open both workbooks and keep the source workbook open when recalculating. Microsoft also documents problems with criteria strings longer than 255 characters, so check the criterion's length when troubleshooting a long text match.
Frequently Asked Questions
Can COUNTIF count cells by their background or font color?
No, COUNTIF cannot directly count font or background colors. Use a condition based on cell values when you need a COUNTIF result.
Which functions count blank or nonblank cells?
Use =COUNTBLANK(A2:A10) to count blanks, including formulas returning empty text. Use =COUNTA(A2:A10) to count cells containing values.
Does COUNTIF need an array formula shortcut?
No, an ordinary COUNTIF formula does not need Ctrl + Shift + Enter. Press Enter on Windows or the web, or Return on Mac.
Can you enter COUNTIF on a phone or tablet?
Yes, with workbook editing available, select the result cell, then double-tap it or tap the formula bar and enter =COUNTIF(A2:A10,"Paid"). On Android, tap the check mark to commit it.
Can COUNTIF use a named range?
Yes, select A2:A10, type Statuses in the Name Box, and press Enter on Windows or Return on Mac. In a result cell, enter =COUNTIF(Statuses,"Paid") and press the same key to calculate. The name replaces the cell range in the formula.