Yes, you can use COUNTIF to count cells that equal a value stored in another cell. The formula is =COUNTIF(range, cell_reference), for example =COUNTIF(A2:A20, C1). This works because COUNTIF accepts a cell reference as its criteria argument, and it will count every cell in the range that exactly matches the value in that referenced cell.
How COUNTIF Handles Cell References
COUNTIF’s second argument is the criteria. When you type a cell reference like C1 directly (without quotes or operators), Excel treats it as “equal to the value in C1.” This is the simplest and most common way to compare a range against a single cell’s value.
If you want to count cells that are not equal to the cell, use <> with the reference: =COUNTIF(A2:A20, "<>"&C1). The ampersand (&) concatenates the operator with the cell’s value, which is necessary because Excel would otherwise interpret <> as text.
Setting Up a Basic Example
Assume you have a list of product names in A2:A20 and you want to count how many times the product name in C1 appears. In any empty cell, enter:
=COUNTIF(A2:A20, C1)
If C1 contains “Widget” and the range has “Widget” three times, the formula returns 3. This is the direct answer to the question.
Using Cell References with Wildcards and Operators
Sometimes you need to count cells that partially match a cell’s value or meet a numeric condition. Here are the patterns:
- Contains text from a cell:
=COUNTIF(A2:A20, "*"&C1&"*")counts cells that contain the text in C1 anywhere. - Starts with text from a cell:
=COUNTIF(A2:A20, C1&"*")counts cells that begin with the value in C1. - Greater than a cell’s number:
=COUNTIF(B2:B20, ">"&C1)counts cells in B2:B20 that are numerically greater than the value in C1. - Less than or equal to a cell’s number:
=COUNTIF(B2:B20, "<="&C1)works similarly.
Note that when using operators, you must enclose the operator in quotes and use the ampersand. The cell reference itself is never inside quotes.
Common Mistakes and How to Avoid Them
A frequent error is putting the cell reference inside quotes: =COUNTIF(A2:A20, "C1"). This makes Excel look for the literal text "C1" instead of the value in C1, so the result is almost always 0. Another mistake is using a range that doesn't match the data size, or referencing a cell that contains a formula whose result changes—COUNTIF will evaluate the current value.
Also, COUNTIF is not case-sensitive. If your data has "apple" and "Apple", both will be counted when the criteria cell contains "apple". If you need case-sensitive counting, you must use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A2:A20, C1)).
Comparing COUNTIF with Other Functions
COUNTIF is ideal for a single condition. For multiple conditions, use COUNTIFS. For example, to count rows where column A equals C1 and column B equals D1, use =COUNTIFS(A2:A20, C1, B2:B20, D1).
If you need to sum values instead of counting, use SUMIF. If you need to return a value from another column when a match is found, use VLOOKUP or INDEX/MATCH. COUNTIF only counts; it doesn't retrieve data.
Using COUNTIF with Dynamic Arrays (Microsoft 365)
In Excel 365, you can use COUNTIF with a spilled array. For instance, if you have a list of criteria in C1:C3, you can enter =COUNTIF(A2:A20, C1:C3) in a single cell, and Excel will spill results down three cells, one count for each criteria. This is a powerful way to get multiple counts without copying formulas.
This behavior requires Excel 365 or Excel 2021. In older versions, you'd need to enter the formula as an array formula (Ctrl+Shift+Enter) or copy it down.
Frequently Asked Questions
Why does my COUNTIF return 0 when the cell has text?
Check that the cell reference is not inside quotes. Also, ensure the range includes the cells you think it does. If the criteria cell contains a formula that returns text, COUNTIF will compare the displayed value, which should work. If the text has extra spaces, use TRIM on the criteria cell or the range.
Can I use COUNTIF with a cell that contains a date?
Yes. If C1 contains a date, =COUNTIF(A2:A20, C1) counts cells in A2:A20 that have the same date. Dates are stored as numbers in Excel, so this works as long as the date formatting is consistent. If you have time components, you may need to use SUMPRODUCT with INT.
How do I count cells that are not equal to a cell value?
Use =COUNTIF(range, "<>"&C1). The ampersand is essential. For example, =COUNTIF(A2:A20, "<>"&C1) counts all cells that do not equal the value in C1.
Does COUNTIF work with blank cells referenced in the criteria?
If C1 is blank, =COUNTIF(A2:A20, C1) counts empty cells in the range. To count non-blank cells, use =COUNTIF(A2:A20, "<>"). To count cells that are not equal to a blank cell (i.e., non-blank), use =COUNTIF(A2:A20, "<>"&C1) where C1 is blank—this will count non-blank cells, because the criteria becomes "<>" (not equal to nothing).
Can I use a cell reference in COUNTIF with wildcards on both sides?
Yes, for example =COUNTIF(A2:A20, "*"&C1&"*") counts cells that contain the text in C1 anywhere. This is useful for partial matches.
What if my criteria cell contains a number stored as text?
If C1 contains a number stored as text (e.g., from a data import), COUNTIF may not match numeric cells. Convert the criteria to a number with =COUNTIF(A2:A20, VALUE(C1)) or use =COUNTIF(A2:A20, C1+0) if C1 is text.