Function Problems

How to Use a Cell Reference as a COUNTIF Criterion in Excel

Ayan Masood Sep 28, 2026 870 views

Question details

The user needs to count occurrences of specific text within a range using a cell reference (such as C5) as the COUNTIF criterion, and then drag the formula down for approximately 1,000 rows.

How to Use a Cell Reference as a COUNTIF Criterion in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic COUNTIF formula to analyze a large dataset where the search criteria updates automatically when filled down.
Observed behavior: The user wants to correctly format the formula after previously encountering errors caused by improperly placing quotation marks or asterisks around the cell reference.

Before you start

Before dragging your formula down across thousands of rows, ensure your lookup range (e.g., O8:T22) is locked with absolute references (like $O$8:$T$22) so the search area does not shift.

Solution 1Recommended

Use the Cell Reference Directly in the COUNTIF Formula

Insert the cell reference without any quotation marks so Excel recognizes it as a dynamic reference rather than literal text.

When using a cell reference as a criterion in the COUNTIF function, you must leave the reference outside of quotation marks. If you enclose it in quotes, Excel will count cells containing the exact string instead of the value inside the referenced cell.

Select the target cell

Click on the cell where you want the first count result to appear.

Enter the formula

Type =COUNTIF($O$8:$T$22, C5) into the formula bar. Notice that C5 is not enclosed in quotation marks.

Apply the calculation

Press Enter to calculate the occurrences of the text located in cell C5.

Fill the formula down

Click and hold the fill handle (the small square at the bottom-right corner of the selected cell) and drag it down. The criterion will automatically change to C6, C7, and so on.

Use the Cell Reference Directly in the COUNTIF Formula

Using Absolute References: Adding dollar signs ($) to the range O8:T22 ensures the search block stays completely locked while the criterion (C5) updates relative to each row.

Advanced Data Analysis Made Easy

Use COUNTIF with Dynamic References in WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly data analysis tools. It fully supports standard Excel functions like COUNTIF, making it incredibly easy to drag and apply formulas across thousands of rows seamlessly.

1. Open your workbook: Launch WPS Spreadsheet and open your document containing the data.
2. Input the COUNTIF function: Select the empty cell next to your first criteria value and type =COUNTIF($O$8:$T$22, C5).
3. Drag to fill: Press Enter, then double-click the green fill handle at the bottom-right of the cell to instantly fill the formula down for all 1,000+ rows.

100% format compatibility with Microsoft Excel (.xlsx) files and formulasLightweight architecture for smooth handling of massive datasetsFamiliar user interface requiring zero learning curveCompletely free to use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIF formula returning 0 when referencing a cell?

This happens if you accidentally enclose the cell reference in quotation marks (e.g., =COUNTIF(Range, "C5")). Excel searches for the literal text 'C5' instead of the data contained within the cell. Remove the quotation marks to fix this.

How do I use greater than or less than operators with a cell reference in COUNTIF?

To combine logical operators with a cell reference, place the operator in quotation marks and use an ampersand (&) to join it to the cell. For example, to count cells greater than the value in C5, use =COUNTIF(Range, ">"&C5).

Can I use wildcards with a cell reference in COUNTIF?

Yes. If you want to count cells that contain the text of C5 anywhere within them, you must concatenate the wildcards using ampersands. The correct formula is =COUNTIF(Range, "*"&C5&"*").

Excel & Data Problems