This Excel tutorial shows how to count entries with COUNTIF so that a column of orders, names, or statuses turns into a single, reliable number. The function checks a range of cells against one condition and returns how many cells match. Setting it up takes under a minute once the pattern is clear.
Key Takeaways
- The formula pattern is =COUNTIF(range, criteria), where range is the cells to check and criteria is what to match.
- Text criteria go inside quotation marks, such as =COUNTIF(B2:B50,’Shipped’), while a cell reference needs no quotation marks.
- Comparison operators like greater than or not equal to must also be wrapped in quotation marks, for example ‘>100’.
- Wildcards such as * and ? let the formula match partial text.
- COUNTIF is not case-sensitive, so ‘shipped’ and ‘SHIPPED’ count as the same entry.
Step-by-Step Guide to Count Entries With COUNTIF
Picture a sales sheet with order statuses in column B, from B2 to B50. The goal is to find out how many orders are marked Shipped. The same steps work for any list, whether it holds names, regions, scores, or dates.
- Step 1: Click an empty cell where the result should appear, such as D2. Keeping the result outside the data range prevents the formula from counting itself.
- Step 2: Type =COUNTIF( to start the formula. Excel displays a tooltip showing the two arguments, range and criteria.
- Step 3: Select the range to check by dragging across B2:B50, or type it directly. The formula now reads =COUNTIF(B2:B50.
- Step 4: Type a comma, then enter the criteria. For text, wrap the word in quotation marks, so the formula becomes =COUNTIF(B2:B50,’Shipped’).
- Step 5: Close the parenthesis and press Enter. The cell displays the number of entries in B2:B50 that equal Shipped.
- Step 6: Check the result by filtering the column or glancing through the list. A quick sanity check catches typos in the criteria before the number gets used in a report.

Tip: Instead of typing the word Shipped inside the formula, type it in a cell such as D1 and use =COUNTIF(B2:B50,D1). Changing D1 to Pending instantly recounts without editing the formula.
Count Entries That Match a Number or Comparison
COUNTIF handles numbers just as easily. To count scores above 80 in C2:C50, use =COUNTIF(C2:C50,’>80′). To count cells equal to a specific number, such as 100, use =COUNTIF(C2:C50,100) with no quotation marks needed. Other operators follow the same pattern: ‘<50’ for less than, ‘>=75’ for greater than or equal to, and ‘<>0’ for anything that is not zero.
Count Entries With Partial Text Using Wildcards
Sometimes the entry is not an exact match. A list of product codes might include North-East, North-West, and North-Central, and the goal is to count everything starting with North. The formula =COUNTIF(A2:A50,’North*’) does exactly that, since the asterisk stands for any number of characters. A question mark stands for exactly one character, so ‘Q?’ matches Q1 and Q2 but not Q10.

Count Blank and Non-Blank Entries
To count empty cells in a range, use =COUNTIF(B2:B50,”). To count cells that contain anything at all, use =COUNTIF(B2:B50,'<>’). This is handy for checking how many rows in a form-style sheet have been filled in.
Tip: Press F4 while the cursor is on a range reference to lock it as $B$2:$B$50. This keeps the range fixed when the formula is copied to other cells.
Common Problems and Fixes
The Formula Returns 0 When Matches Clearly Exist
Extra spaces are the usual cause. An entry like ‘Shipped ‘ with a trailing space does not equal ‘Shipped’. Clean the data with the TRIM function in a helper column, or use a wildcard such as ‘Shipped*’ to catch the variations.
Numbers Stored as Text Are Not Counted
If numbers are left-aligned with a small green triangle in the corner, they are stored as text. Select the cells, click the warning icon that appears, and choose Convert to Number. The COUNTIF result then updates correctly.
The Formula Shows #NAME?
This error appears when the function name is misspelled or when text criteria are missing quotation marks. Check that the function is spelled COUNTIF and that words like Shipped are wrapped in quotation marks.
The Result Does Not Match a Count of Long Text Strings
COUNTIF cannot match text strings longer than 255 characters and returns a #VALUE! error in that case. Shorten the criteria or use a different approach for those entries.
The Count Does Not Update After Data Changes
If calculation is set to manual, formulas do not refresh on their own. Open the Formulas tab, choose Calculation Options, and select Automatic.
COUNTIF Across a Different Sheet or Closed Workbook Fails
When the range lives on another worksheet, the reference must include the sheet name, such as =COUNTIF(Orders!B2:B50,’Shipped’). For ranges in another workbook, keep that workbook open, since COUNTIF returns a #VALUE! error when the source file is closed.
Frequently Asked Questions
What is the basic COUNTIF formula in Excel?
The basic formula is =COUNTIF(range, criteria). The range is the group of cells to check, and the criteria is the value or condition a cell must meet to be counted.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as the same, so ‘Yes’, ‘yes’, and ‘YES’ are all counted together.
How do I count entries greater than a certain number?
Place the operator and number inside quotation marks. For example, =COUNTIF(C2:C50,’>80′) counts every cell in C2:C50 with a value above 80.
Can COUNTIF count cells that contain specific text within a longer entry?
Yes. Wrap the text in asterisks, such as =COUNTIF(A2:A50,’*invoice*’), to count every cell that contains the word invoice anywhere in the entry.
How do I count blank cells with COUNTIF?
Use =COUNTIF(B2:B50,”) with empty quotation marks. This returns the number of empty cells in the range.
Can COUNTIF use a cell reference as the criteria?
Yes. Enter the value in a cell and reference it, for example =COUNTIF(B2:B50,D1). No quotation marks are needed around a cell reference. To combine an operator with a reference, join them with an ampersand, such as ‘>’&D1.
How do I count entries that match more than one condition?
COUNTIF handles only one condition. For two or more conditions, use COUNTIFS, which follows the pattern =COUNTIFS(range1, criteria1, range2, criteria2).
Why does COUNTIF count a number typed as text?
COUNTIF treats a number and the same number stored as text as matching in many cases, which can lead to a higher count than expected. Converting the cells to true numbers or text consistently keeps the result predictable.
Does COUNTIF work with dates?
Yes. Use a comparison such as =COUNTIF(A2:A50,’>=’&DATE(2026,1,1)) to count dates on or after January 1, 2026. Excel stores dates as numbers, so the operators work the same way as with any other number.