COUNTIF Excel: How to Reference Cells in Criteria

To make COUNTIF use a cell as its criterion, put the cell reference in the second argument: with values in A2:A20 and the value to count in D2, enter =COUNTIF(A2:A20,D2). Excel counts cells in A2:A20 that match the value in D2; when the criterion includes an operator such as “greater than,” join the operator text to the reference with an ampersand.

How COUNTIF uses a cell reference

COUNTIF has two arguments: the range to examine and the criterion to apply. The criterion can be typed directly into a formula, or it can come from another cell. Referencing a cell is useful when you want to change the condition without editing the formula each time, or when a worksheet user should be able to choose a value from a dropdown.

For example, suppose A2:A8 contains Paid, Pending, Paid, Cancelled, Paid, Pending, and Paid, while D2 contains Paid. With those values in place, enter =COUNTIF(A2:A8,D2) in E2. The result is 4, because four cells in A2:A8 match D2. If D2 changes to Pending, the result recalculates to 2.

The criterion cell can be anywhere in the workbook, provided the formula refers to it correctly. It does not have to be beside the data range. COUNTIF compares each cell in the selected range against the current criterion; it does not change the source data.

Use a cell reference for an exact match

For a straightforward match, use the criterion cell as the second argument without quotation marks around the reference. With product names in A2:A100 and the item to count in D2, enter =COUNTIF(A2:A100,D2). If D2 contains Notebook, Excel counts cells whose contents match Notebook. The formula does not need to be rewritten when you replace that value with another product name.

Do not confuse a reference with text that happens to look like a reference. In =COUNTIF(A2:A100,"D2"), the quoted characters D2 are the criterion, so Excel looks for cells containing the text D2. In =COUNTIF(A2:A100,D2), Excel reads the value currently stored in cell D2 and uses that value as the criterion.

COUNTIF matching is not case-sensitive: “North” and “north” are treated as the same criterion. It can distinguish different text, numbers, and dates, but it is not a tool for case-sensitive counting. If capitalization itself matters, use a case-sensitive method such as a formula based on EXACT rather than expecting COUNTIF to distinguish letter case.

Reference a cell with comparison operators

When a criterion uses greater-than, less-than, or not-equal logic, the operator must be part of the criterion text. Join that text to the cell reference with &. For example, if scores are in B2:B20 and the cutoff is in D2, use the following formula to count scores above the cutoff: =COUNTIF(B2:B20,">"&D2). If D2 contains 80, the criterion becomes greater than 80; a score equal to 80 is not counted.

For an inclusive comparison, use a two-character operator. With scores in B2:B20 and the minimum score in D2, enter =COUNTIF(B2:B20,">="&D2). If D2 is 80, this counts scores of 80 or higher. The operator is enclosed in quotes, while the reference is outside the quotes. The ampersand joins them into one criterion.

For not equal, use the same pattern. With status values in A2:A20 and the excluded status in D2, enter =COUNTIF(A2:A20,"<>"&D2). If D2 contains Closed, the formula counts cells that are not Closed. Be aware that COUNTIF may also count blank cells for a not-equal criterion, so consider whether blanks should be included in the result.

Count dates and numbers from a criterion cell

Excel stores dates as serial numbers, even though cells usually display them in a calendar format. A date entered in a criterion cell can therefore be used directly or combined with a comparison operator. With order dates in B2:B100 and the start date in F2, enter =COUNTIF(B2:B100,">="&F2) to count dates on or after the date in F2. Make sure F2 contains a real Excel date, not text that only looks like a date.

COUNTIF counts dates on or after that cutoff without imposing an end date. To count dates within a period, use COUNTIFS, which supports more than one condition. With order dates in B2:B100, a period start date in F2, and an end date in G2, enter =COUNTIFS(B2:B100,">="&F2,B2:B100,"<="&G2). This counts dates from the start through the end, including both endpoints.

Similarly, a numeric criterion can be supplied from a cell for counts such as values above a target, below a limit, or equal to a selected amount. If the formula seems to ignore a date or number, check the underlying cell type as well as its display format: a text value that resembles a number may not compare as expected.

Use wildcards when the cell contains only part of the match

COUNTIF supports two wildcards in text criteria: an asterisk (*) represents any number of characters, and a question mark (?) represents one character. If D2 contains a search term, you can add wildcard characters around the reference to count cells that contain that term anywhere in their text. With descriptions in A2:A50 and the search text in D2, enter =COUNTIF(A2:A50,"*"&D2&"*"). If D2 contains ship, this can match descriptions such as “Shipping fee” and “Express shipment.”

Wildcards also work for patterns. With product codes in A2:A50 and a single-character code fragment in D2, the criterion =COUNTIF(A2:A50,D2&"?") counts entries that begin with the value in D2 and have exactly one additional character. For a literal asterisk or question mark in the data, precede the wildcard with a tilde (~) in the criterion; otherwise Excel interprets it as a pattern character.

A common surprise is that a plain reference asks for an exact match, while adding asterisks changes the meaning to “contains.” Choose the version based on the task. Wildcard matching is for text patterns; it is not a substitute for numeric comparisons or date boundaries.

Make references stay in place when copying formulas

A relative reference changes when you copy a formula. If the formula in E2 refers to D2, copying it down one row changes that reference to D3. This is useful when each row has its own criterion. If every copied formula should use one shared criterion cell, lock the reference with dollar signs.

With amounts in A2:A20 and one shared threshold in D1, enter this formula in B2 and copy it down: =COUNTIF(A2:A20,">"&$D$1). The dollar signs keep D1 fixed. The range A2:A20 is relative in this example, so copying the formula down shifts the range as well. If the formula should always examine the same range, lock that too: =COUNTIF($A$2:$A$20,">"&$D$1).

In Excel for Microsoft 365 on Windows, pressing F4 while editing a reference cycles through relative, absolute, and mixed reference forms. On many Mac keyboards, use Command+T to cycle reference types; depending on keyboard settings, you may need Fn+F4. You can also type the dollar signs directly. A mixed reference such as $D2 locks the column but allows the row to change, while D$2 locks the row but allows the column to change.

Refer to another worksheet or an Excel table

A criterion cell can be on a different worksheet. If the source values are on a sheet named Orders in A2:A100 and the criterion is in D2 on the current sheet, use =COUNTIF(Orders!A2:A100,D2). If a sheet name contains spaces, enclose it in single quotation marks. With the range on a sheet named Order Data and the criterion in D2, use =COUNTIF('Order Data'!A2:A100,D2).

Excel tables can make formulas easier to read and maintain. With a table named Sales and a column named Status, plus the selected status in D2, use =COUNTIF(Sales[Status],D2). The structured reference identifies the table column, and the table expands as rows are added. To check a table’s name, select a cell in it and look for the Table Design tab; the table name appears in the Properties group.

In Excel for the web and Excel for Mac, the COUNTIF formula logic and these reference patterns are the same. Ribbon labels or keyboard shortcuts can differ slightly by platform and version, but the formula itself does not need a platform-specific alternative. The formulas here use commas as argument separators; some regional settings use semicolons, so Excel may require that separator in a locally configured installation.

Common errors and ways to check the result

If a formula returns zero unexpectedly, first inspect the criterion cell. It may contain an extra space, a different spelling, a number stored as text, or a date stored as text. Also check that the range includes the rows you intend to count and that the criterion cell reference points to the correct cell. The formula can be syntactically valid while referring to the wrong range.

When a formula is copied, check whether a relative reference moved. This is especially important if the criterion cell is a shared control value. Use dollar signs to anchor it, then test the formula by changing the criterion cell to a value whose count is easy to verify. For troubleshooting, compare the result with a small range where you can manually count the matches.

Use a plain cell reference for an exact match, for example a status selected in D2.
Join an operator to a reference with & for a threshold such as greater than the value in D2.
Use wildcards only when you want a text pattern or a partial text match.
Use COUNTIFS when the count must satisfy multiple conditions, such as a start and end date.

COUNTIF accepts one criterion, so separate COUNTIF formulas are not always equivalent to a multi-condition count. If the question is “How many rows have Region equal to East and Status equal to Paid?”, COUNTIFS is the more direct function. With regions in A2:A100, statuses in B2:B100, and the requested values in D2 and E2, enter =COUNTIFS(A2:A100,D2,B2:B100,E2). That counts rows meeting both conditions on the same row.

Frequently Asked Questions

Can COUNTIF use a cell reference as its criteria?
Yes. Put the reference in the second argument, such as =COUNTIF(A2:A20,D2). Excel uses the value in D2 as the criterion and updates the result when that value changes.

How do I use greater than with a cell reference in COUNTIF?
Join the quoted operator to the reference with an ampersand. For example, use =COUNTIF(B2:B20,">"&D2) to count values in B2:B20 that are greater than the number in D2.

Do I put quotation marks around a cell reference?
No. A reference such as D2 should not be quoted when you want Excel to read the value in that cell. Quotes are used for literal text or for the operator portion of a criterion, such as “>”.

How can I count cells that contain the text in another cell?
Add asterisks around the referenced text using concatenation. With descriptions in A2:A50 and a search term in D2, =COUNTIF(A2:A50,"*"&D2&"*") counts cells containing that term anywhere in the text.

Why does my COUNTIF formula change when I copy it?
Excel adjusts relative references based on the new formula location. Add dollar signs to references that must stay fixed, such as $D$1 for a shared criterion cell, and lock the range too if it must not move.

Can COUNTIF use two criteria cells?
COUNTIF handles one condition. Use COUNTIFS for multiple conditions, supplying a range and criterion for each condition. For example, it can count rows where both a region and a status match the values selected in two separate cells.

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.