Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesUse a cell reference directly in COUNTIF to count exact matches, or join a quoted comparison operator to a reference with & to compare against a threshold. For example, =COUNTIF(A2:A20,D1) counts cells matching the value in D1, while =COUNTIF(B2:B20,">"&D1) counts values greater than D1.
How do I use a cell reference in COUNTIF?
The syntax is =COUNTIF(range,criteria). The range is the cells Excel checks, and criteria is the condition to match. For an exact match to the value in another cell, enter:
=COUNTIF(A2:A20,D1)
This counts cells in A2:A20 whose contents match the value in D1. The reference can be a number, text value, or other criterion held in a cell. Microsoft documents this direct-reference pattern in its guide to cell references in criteria.
How do I combine a comparison operator with a cell reference in COUNTIF?
Put the operator in quotation marks and join it to the reference with the ampersand (&). For example:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute=COUNTIF(B2:B20,">"&D1)counts values greater than the threshold in D1.=COUNTIF(B2:B20,"<>"&D1)counts values that are not equal to D1.
The quoted operator is text; & joins it to the value from D1 to create the criterion Excel evaluates. If you need to build a criterion in a separate cell, a formula such as =">"&$D$1 creates a greater-than criterion using an absolute reference.
How do I count text that starts with a referenced value?
Append the asterisk wildcard to the referenced text:
Rank #2
- Used Book in Good Condition
=COUNTIF(A2:A20,D1&"*")
This counts cells in A2:A20 that begin with the text in D1. The * wildcard represents any sequence of characters, including no characters. A question mark (?) represents exactly one character; use a tilde (~) before * or ? when you want to match that character literally. Text comparisons in COUNTIF are not case-sensitive.
When should I use COUNTIFS instead?
COUNTIF applies one criterion. If every one of multiple conditions must be true, use COUNTIFS and pair each range with its criterion:
Free tools Windows power users keep installed
One-click scans. No signup required.
=COUNTIFS(A2:A20,D1,B2:B20,">"&E1)
This example counts rows where the corresponding A cell matches D1 and the corresponding B cell is greater than E1. Microsoft says COUNTIFS supports up to 127 range-and-criterion pairs. See the COUNTIFS function documentation for its syntax and behavior.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What should I check if COUNTIF returns an unexpected result?
- Text does not match: Check for leading or trailing spaces, nonprinting characters, and straight quotation marks in the formula.
TRIMcan remove extra spaces andCLEANcan remove certain nonprinting characters. - Case differs: COUNTIF text criteria are not case-sensitive, so it cannot distinguish upper- and lowercase matches.
- Wildcards match too much: Remember that
*matches a sequence and?matches one character. Escape a literal wildcard with~. - Long text gives an incorrect count: Microsoft warns of incorrect results for strings longer than 255 characters and recommends concatenating string pieces for that situation.
- The formula returns #VALUE!: Microsoft documents this error when COUNTIF refers to a range in a closed external workbook and the cells are calculated. The referenced workbook must be open for that case.
- You need to count by color: COUNTIF does not count by background or font color; Microsoft notes that this requires a VBA user-defined function.
For the complete syntax and additional examples, see Microsoft’s COUNTIF function guide.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
