Excel COUNTIF Matches Reference Cell: Formula & Tips

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.