Part of the free Module 5: Excel Formulas and Functions · Function 16 of 102 · Full Excel course
The Excel COUNTIF function counts the cells in a range that meet one condition, such as how many orders say “Delivered” or how many scores are above 50. COUNTIF in Excel returns a whole number and ignores case. When you need two or more conditions together, switch to COUNTIFS.
COUNTIF syntax
=COUNTIF(range, criteria)Arguments
| Argument | Required | Meaning |
|---|---|---|
| range | Required | The cells to count. |
| criteria | Required | The condition: a value (50), text (“apple”), a comparison in quotes (“>=50”, “<>0”), a wildcard pattern (“*cat*”) or a cell reference. |
Step-by-step example
Column A holds a list of months and you want to know how often a particular month appears. With the criterion in E5:
=COUNTIF($A$2:$A$10, E5)- Excel reads the value in E5, for example “Jan”.
- It compares every cell in A2:A10 with that value.
- Each matching cell adds 1; the formula returns the frequency.
The screenshot below shows COUNTIF returning how many times the chosen value appears in the list.
Practical use cases
1. Count a text value
=COUNTIF(B2:B50, "apple")2. Count numbers above a limit
=COUNTIF(C2:C50, ">=50")3. Count non-zero or non-blank cells
=COUNTIF(D2:D50, "<>0")
=COUNTIF(D2:D50, "<>")4. Partial text with wildcards
=COUNTIF(E2:E50, "*cat*")Counts every cell containing “cat” anywhere. “cat” alone counts only cells that are exactly cat.
5. Find duplicates
=COUNTIF($A$2:$A$500, A2)>1Fill down: TRUE marks any value that appears more than once. The same test works as a conditional-formatting rule.
6. Dates before today
=COUNTIF(F2:F50, "<"&TODAY())Common mistakes and errors
- Count is 0 – numbers stored as text, extra spaces, or a comparison typed without quotes. Clean with TRIM/VALUE and write “>=50”.
- #VALUE! – the range points to a closed workbook, or the criteria string exceeds 255 characters.
- #NAME? – function misspelt, or text criteria not wrapped in quotes.
- Counting by colour – COUNTIF reads values only. Use a helper column or VBA to count by fill colour.
- Case – COUNTIF is not case-sensitive. Use SUMPRODUCT(–EXACT(range, “ABC”)) when case matters.
Tips and best practices
- Reference the criteria cell rather than typing text into the formula; a summary table then needs one COUNTIF filled down.
- Use COUNTIF for validation: a custom Data Validation rule =COUNTIF($A:$A, A2)=1 prevents duplicate IDs at entry time.
- Combine with wildcards to group categories, e.g. COUNTIF(B:B, “Inv-*”) counts every invoice code.
- Check the data type when counts look wrong: =ISNUMBER(A2) beside the column exposes numbers stored as text.
- Move to COUNTIFS as soon as a second condition appears; the criteria rules are identical.
Related functions
- COUNTIFS – multiple conditions.
- SUMIF – adds values that meet a condition.
- AVERAGEIF – averages values that meet a condition.
- IF – tests a condition on a single cell.
- Browse the Excel Formulas hub or read Common errors in Excel formulas.
Video tutorial
Frequently asked questions
Can COUNTIF count cells by colour or font style?
No. COUNTIF evaluates cell values only. To count by fill colour, add a helper column that records the colour with a VBA function, then COUNTIF on that column.
Can COUNTIF count across multiple worksheets?
Not in one range. Add separate COUNTIF results for each sheet: =COUNTIF(Jan!A:A, “x”)+COUNTIF(Feb!A:A, “x”), or consolidate the data first.
How do I count cells that contain any text?
Use the wildcard “*”: =COUNTIF(A2:A50, “*”) counts every cell with text, ignoring numbers and blanks.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.