Excel COUNTIF Value in Cell? Formula & Examples

Formulas & functions · Updated November 22, 2026

The direct answer: to count cells that equal a value stored in another cell, use =COUNTIF(range, cell_reference), for example =COUNTIF(A2:A20, B1). This counts every cell in A2:A20 that exactly matches the text or number in B1.

What COUNTIF Does and Why It Matters

COUNTIF is one of Excel’s oldest and most-used functions. It counts cells within a range that meet a single condition. The condition can be a number, text, a logical expression, or a reference to another cell. When you use a cell reference instead of typing a value directly, the formula updates automatically if the referenced cell changes. That makes it ideal for dashboards, reports, and any spreadsheet where the criteria might be edited without rewriting formulas.

For example, a sales manager might keep a list of regions in column A and want to know how many times “North” appears. Typing =COUNTIF(A2:A100,"North") works, but if the region name is in cell D1, using =COUNTIF(A2:A100,D1) lets you change D1 to “South” and instantly get a new count. This is the core of the “value in cell” question.

How to Write COUNTIF with a Cell Reference

Assume your data is in A2:A20 and the value you want to match is in B1. The formula is:

=COUNTIF(A2:A20, B1)

This returns the number of cells in A2:A20 that exactly equal the contents of B1. If B1 contains the text “Apple”, it counts cells that say “Apple” (case-insensitive). If B1 contains the number 5, it counts cells with the number 5. If B1 is empty, COUNTIF returns 0 because it counts cells equal to an empty string, which is rare.

You can also combine a cell reference with a wildcard. For instance, to count cells that start with the text in B1, use:

=COUNTIF(A2:A20, B1&"*")

This counts cells where the value begins with whatever is in B1. The ampersand (&) joins the text in B1 with the asterisk wildcard. Similarly, =COUNTIF(A2:A20, "*"&B1&"*") counts cells containing B1 anywhere.

For partial matches that are case-sensitive, you would need a different approach, as COUNTIF is not case-sensitive. Use SUMPRODUCT or a helper column with EXACT for that.

How to Choose Between COUNTIF and Other Functions

COUNTIF is the right tool when you have one condition. But if you need multiple conditions, use COUNTIFS. For example, to count rows where column A equals B1 and column C is greater than 100:

=COUNTIFS(A2:A20, B1, C2:C20, ">100")

COUNTIFS accepts multiple range/criteria pairs. Each range must be the same size. If you only need to count numeric values greater than a number stored in a cell, you can still use COUNTIF with a comparison operator joined by an ampersand:

=COUNTIF(D2:D20, ">"&B1)

Here B1 contains the threshold number. The ampersand turns the operator and number into a text string like “>50”, which COUNTIF interprets correctly.

If you need to count unique values, use SUMPRODUCT with COUNTIF, or the newer UNIQUE function (available in Excel 365 and Excel 2021). For example, to count unique values in A2:A20:

=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))

This works but can be slow on large ranges. A simpler alternative in Excel 365 is =COUNTA(UNIQUE(A2:A20)).

Common Problems and How to Fix Them

Several issues trip up users when using COUNTIF with a cell reference.

  • Counting text that looks like a number: If B1 contains a number formatted as text (e.g., from a data import), COUNTIF may not match numeric cells. Convert B1 to a number with =VALUE(B1) or re-enter it.
  • Trailing spaces: A cell like “Apple ” (with a space) won’t match “Apple”. Use TRIM on the data or the criteria: =COUNTIF(A2:A20, TRIM(B1)).
  • Case sensitivity: COUNTIF ignores case, so “apple” matches “APPLE”. If you need case-sensitive counting, use =SUMPRODUCT(--EXACT(A2:A20, B1)).
  • Counting blank cells: To count truly empty cells, use =COUNTBLANK(A2:A20). COUNTIF with “” counts cells that contain an empty string (which can result from formulas), but not truly blank cells.
  • Error values: If your range contains #N/A errors, COUNTIF will ignore them, but it won’t count them. To count errors, use =SUMPRODUCT(--ISERROR(A2:A20)).

Another common misconception is that COUNTIF can count cells based on the cell’s fill color. It cannot. COUNTIF only evaluates values, not formatting. For color-based counting, you need VBA or the newer CELL function with a macro, which is not recommended for beginners.

Working with Dates and Times

Dates in Excel are stored as serial numbers. When you use a cell reference for a date, it works fine. For example, if B1 contains a real date (e.g., 1/1/2025), =COUNTIF(A2:A20, B1) counts cells with that exact date. However, if you type a date as text in the formula, you must use =COUNTIF(A2:A20, DATE(2025,1,1)) or =COUNTIF(A2:A20, "1/1/2025") with quotes. Using a cell reference is safer because it avoids regional date format issues.

To count dates after a certain date stored in B1, use =COUNTIF(A2:A20, ">"&B1). This counts all dates greater than the date in B1. Remember that times are fractions of a day, so a time like 12:00 PM is 0.5. Counting times works the same way.

Performance and Large Data Sets

COUNTIF is fast for ranges up to tens of thousands of cells. But if you use it on an entire column (e.g., A:A), it can slow down because it processes all 1,048,576 rows. It’s better to limit the range to your actual data. If you have a dynamic range that changes size, consider using a table (Insert > Table) and referencing the column name, like =COUNTIF(Table1[Column1], B1). This automatically adjusts as you add rows.

For very large datasets (hundreds of thousands of rows), COUNTIFS or SUMPRODUCT can be slower. In Excel 365, you might use the newer functions like FILTER and COUNTA, but for simple counting, COUNTIF is usually sufficient.

Alternatives to COUNTIF for Specific Needs

Depending on your goal, other functions might be more appropriate.

  • COUNTIFS – for multiple conditions (as shown above).
  • COUNT – counts numeric cells only, ignoring text and blanks. =COUNT(A2:A20).
  • COUNTA – counts non-empty cells, including text and numbers. =COUNTA(A2:A20).
  • COUNTBLANK – counts truly empty cells.
  • SUMPRODUCT – for complex criteria, like case-sensitive matches or counting based on multiple arrays.
  • FREQUENCY – for counting within bins, though it requires an array formula (in older Excel) or dynamic arrays in 365.
  • UNIQUE + COUNTA – for counting unique values in Excel 365/2021.

For example, to count cells that are not equal to a value in B1, use =COUNTIF(A2:A20, "<>"&B1). This counts all cells that are not equal to B1, including blanks. If you want to exclude blanks, use =COUNTIFS(A2:A20, "<>"&B1, A2:A20, "<>").

Frequently Asked Questions

Can COUNTIF use a cell reference for the range?

Yes, the range can be a cell reference or a named range. For example, =COUNTIF(MyData, B1) where MyData is a named range. The range must be a contiguous block of cells.

Why is my COUNTIF returning 0 when I know there are matches?

Check for leading/trailing spaces, number stored as text, or the criteria cell containing an invisible character. Use =TRIM(B1) in the formula or clean the data. Also, ensure the range is correct and doesn’t include header rows that might not match.

How do I count cells that contain a word from a list of values?

Use SUMPRODUCT with ISNUMBER and SEARCH, or use COUNTIFS with multiple criteria. For a list in D1:D5, you can use =SUMPRODUCT(--(COUNTIF(A2:A20, "*"&D1:D5&"*")>0)) but this is complex. A simpler approach is to use a helper column with a formula like =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$5,A2)))>0,1,0) and then sum that column.

Can I use COUNTIF with a cell reference that contains a wildcard like * or ?

Yes, if B1 contains an asterisk, COUNTIF treats it as a wildcard. For example, if B1 is “A*”, it counts cells starting with A. To count a literal asterisk, use a tilde: =COUNTIF(A2:A20, "~*"&B1) but this gets tricky. Better to use =COUNTIF(A2:A20, SUBSTITUTE(B1, "*", "~*")) to escape all asterisks.

Does COUNTIF work on multiple sheets?

No, COUNTIF works on a single range. To count across sheets, use a 3D reference like =COUNTIF(Sheet1:Sheet3!A2:A20, B1) but this only works if the sheets are adjacent. Alternatively, use multiple COUNTIFs added together: =COUNTIF(Sheet1!A2:A20, B1)+COUNTIF(Sheet2!A2:A20, B1).

Is there a way to make COUNTIF case-sensitive?

Not directly. Use =SUMPRODUCT(--EXACT(A2:A20, B1)) for exact case-sensitive matches. This works in all Excel versions. Note that EXACT returns TRUE for exact matches including case, and the double negative converts TRUE/FALSE to 1/0.

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.