Yes, you can use COUNTIF to count cells that match a reference cell by pointing the criteria argument at the cell reference instead of typing text. For example, =COUNTIF(A2:A20, B1) counts every cell in A2:A20 that equals the value in B1.
How COUNTIF Works with a Reference Cell
COUNTIF takes two arguments: the range to examine and the criteria. When the criteria is a reference to a cell, Excel compares each cell in the range to the value in that reference cell. The comparison is case-insensitive, so “apple” and “APPLE” are treated as equal. If the reference cell contains text, COUNTIF counts exact matches of that text (including spaces and punctuation). If the reference cell contains a number, it counts numeric equality.
Basic Formula Examples
Assume you have a list of product names in A2:A20 and a lookup value in B1. To count how many cells in the list equal B1:
- Enter
=COUNTIF(A2:A20, B1)in a blank cell. If B1 contains “Widget”, the formula returns the number of cells in A2:A20 that contain exactly “Widget”. - To count cells that do not match the reference, use
=COUNTIF(A2:A20, "<>"&B1). The ampersand concatenates the not-equal operator with the reference value. - To count cells that contain the reference text as a substring, use wildcards:
=COUNTIF(A2:A20, "*"&B1&"*"). This counts cells that have the text from B1 anywhere within them.
Using COUNTIF with Partial Matches and Wildcards
Wildcards let you match patterns rather than exact text. The asterisk (*) represents any number of characters, and the question mark (?) represents a single character. To count cells that start with the text in B1, use =COUNTIF(A2:A20, B1&"*"). To count cells that end with the text, use =COUNTIF(A2:A20, "*"&B1). Remember that wildcards only work with text; if B1 is a number, COUNTIF treats it as a literal number unless you convert it to text.
Choosing Between COUNTIF, COUNTIFS, and SUMPRODUCT
COUNTIF is best for a single condition. If you need to match a reference cell plus another condition (like a date range), use COUNTIFS. For example, to count rows where column A equals B1 and column C is greater than 100, use =COUNTIFS(A2:A20, B1, C2:C20, ">100"). If you need case-sensitive matching, COUNTIF cannot do it; use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A2:A20, B1)). This formula checks each cell in A2:A20 against B1 exactly, including case, and sums the TRUE results.
Common Problems and Fixes
- Leading/trailing spaces: If the reference cell has extra spaces, COUNTIF won’t match. Use
=COUNTIF(A2:A20, TRIM(B1))or clean the data with TRIM. - Numbers stored as text: If B1 is a number but the range contains text-formatted numbers, COUNTIF may return 0. Convert the reference to text:
=COUNTIF(A2:A20, TEXT(B1, "0"))or use=COUNTIF(A2:A20, ""&B1). - Blank reference cell: If B1 is empty, COUNTIF will count all blank cells in the range. To avoid this, use
=IF(B1="", 0, COUNTIF(A2:A20, B1)). - Wildcard characters in the reference: If B1 contains an asterisk or question mark, COUNTIF treats it as a wildcard. To match a literal asterisk, escape it with a tilde:
=COUNTIF(A2:A20, SUBSTITUTE(B1, "*", "~*")).
Comparing COUNTIF with Other Functions
COUNTIF is often compared with the newer COUNTIFS (multiple criteria) and with SUMPRODUCT (more flexible). For simple counting, COUNTIF is faster and easier to read. For counting with multiple conditions, COUNTIFS is the direct upgrade. SUMPRODUCT is more powerful but can be slower on large datasets and requires array-style logic. If you need to count unique items that match a reference, you can combine COUNTIF with SUMPRODUCT: =SUMPRODUCT((A2:A20=B1)/COUNTIF(A2:A20, A2:A20&"")) counts unique values equal to B1, though this is an advanced scenario.
Frequently Asked Questions
Can COUNTIF use a cell reference for the range? Yes, the range can be a reference like A2:A20, or you can use an entire column like A:A, but be aware that counting an entire column can slow down your workbook.
How do I count cells that match a reference in another sheet? Simply include the sheet name in the reference: =COUNTIF(Sheet2!A2:A20, B1). The criteria can still be a local cell reference.
What if I want to count cells that match EITHER of two reference cells? Use two COUNTIF functions added together: =COUNTIF(A2:A20, B1) + COUNTIF(A2:A20, C1). For more than two, consider using SUMPRODUCT with an array: =SUMPRODUCT(--(A2:A20=B1)+--(A2:A20=C1)).
Does COUNTIF work with dates in reference cells? Yes, dates are stored as numbers in Excel, so COUNTIF compares the serial numbers. If the reference cell contains a date, it will count cells with that exact date value.
Why does my COUNTIF return 0 even though I see matching text? Check for invisible characters, non-breaking spaces, or differences in case (though case is usually ignored). Use the LEN function to compare lengths, or use TRIM and CLEAN to remove extra spaces and non-printable characters.
Final Tips for Using COUNTIF with References
When building formulas, use absolute references (like $B$1) if you plan to copy the formula down or across, so the reference doesn’t shift. Also, remember that COUNTIF ignores blank cells unless the criteria is an empty string. If you need to count cells that match a reference and are not blank, combine with additional criteria: =COUNTIFS(A2:A20, B1, A2:A20, "<>"). Finally, test your formula on a small sample to verify the result before applying it to a large dataset.
Related
- Excel Audit Process: Step-by-Step Guide
- Excel Audit Log Template: How to Build One
- Excel Attendance Point Tracker Template: Setup & Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter Multiple Items in Excel: A Clear GuideTo filter for several items in Excel, open the filter arrow in the column heading, clear Select All,…
- Excel Formula Cell Reference Lock: Absolute ReferencesTo lock a cell reference in an Excel formula, add dollar signs before the column letter, row number,…
- Hide a Column If a Cell Is Blank in ExcelYes—but Excel has no built-in setting that automatically hides a column when a cell becomes blank. In Excel…
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
- Excel WEEKDAY vs WORKDAY: What Each Function Does in ExcelExcel’s WEEKDAY function tells you which day of the week a date falls on, as a number; WORKDAY…
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.