Function Problems

How to Use Cell References to Define a COUNTIF Range in WPS Office Spreadsheets

Amos Gikunda Nov 13, 2026 869 views

Question details

The user needs to create a COUNTIF formula in WPS Spreadsheets whose evaluation range starts and ends at cell references stored in B1 and B2, so editing those cells changes the range.

Use Cell References to Define a COUNTIF Range in WPS Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user wants to avoid rewriting the COUNTIF range every time the data area changes and instead control the start and end cells from helper cells.
Observed behavior: The desired behavior is a COUNTIF formula that updates its range dynamically when B1 or B2 is edited.

Before you start

Make sure B1 and B2 contain valid cell references as text, such as A2 and A100, and enter the COUNTIF criteria in B4. The referenced range must be in the same open workbook because INDIRECT does not work with closed external workbooks.

Solution 1Recommended

Create a Dynamic COUNTIF Range with INDIRECT and Cell References

Use this method when you want to change the COUNTIF start and end range by editing B1 and B2, rather than rewriting the formula.

Enter the range references

In WPS Spreadsheets, type the start cell reference in B1, for example A2, and the end cell reference in B2, for example A100. Enter your criteria in B4.

Enter the COUNTIF formula

Click the result cell, type =COUNTIF(INDIRECT(B1):INDIRECT(B2),B4) in the formula bar, and press Enter.

Test the dynamic range

Change B1 or B2 to another valid cell reference, such as A5 or A200, and verify that the COUNTIF result updates to use the new range.

Create a Dynamic COUNTIF Range with INDIRECT and Cell References

INDIRECT is volatile: INDIRECT recalculates whenever the workbook changes and cannot read closed external workbooks.

Create a Flexible COUNTIF Range in WPS Office Spreadsheets

WPS Office Spreadsheets supports COUNTIF and INDIRECT, so you can build a dynamic COUNTIF range controlled by B1 and B2 without changing the formula each time.

1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the data, and select the worksheet where you want the COUNTIF result.
2. Set up the helper cells: Type the starting reference in B1 and the ending reference in B2, for example A2 and A100.
3. Add the formula: Click the result cell, enter =COUNTIF(INDIRECT(B1):INDIRECT(B2),B4), and press Enter.
4. Adjust the range from the cells: Edit B1 or B2 to a new valid reference to change the COUNTIF range without editing the formula.

Update COUNTIF ranges dynamically by editing B1 and B2 instead of rewriting formulasFull support for Microsoft Excel-compatible COUNTIF and INDIRECT functionsOpens and saves .xlsx, .xls, and .csv files with Microsoft Office format compatibilityFree, lightweight, and easy-to-use spreadsheet interface

microsoft office alternative - wps office

Frequently Asked Questions

What should I enter in B1 and B2 for a dynamic COUNTIF range?

Enter valid cell references as text, such as A2 and A100. The INDIRECT function converts those text values into working references used by COUNTIF.

Why does my formula return #REF! after using INDIRECT?

This usually means B1 or B2 contains text that is not a valid cell reference, or the referenced range is in a closed external workbook. Check the helper cells and workbook status.

Can I use this COUNTIF method with a range on another sheet?

It works within the same open workbook if B1 and B2 contain full references such as Sheet2!A2 and Sheet2!A100. INDIRECT cannot read closed external workbooks.

Will the COUNTIF result update automatically when I change B1 or B2?

Yes, INDIRECT is volatile, so the formula recalculates when the workbook changes. In very large workbooks, many INDIRECT formulas may slow performance.

Excel & Data Problems