How to Count a Number Between Two Marker Values in WPS Office Excel
Question details
The user needs to count only the occurrences of a specific number that appear between two marker values, such as between the first 18 and the first 19, in an Excel column.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has a column of numbers and needs to count how many times a target value, such as 5, occurs between the first occurrence of two marker values, such as 18 and 19.
- Observed behavior
- Standard COUNTIF/COUNTIFS formulas return counts for the whole range or by value threshold, but they do not isolate the segment between the first 18 and the first 19.
Confirm that your data is in a single column and that the marker values, for example 18 and 19, both exist in that column. Identify the target number you want to count, such as 5.
Use SUMPRODUCT with INDEX and MATCH to Count Between First Markers
Use this formula when you need to count a target value between the first occurrences of two marker values in any WPS Office Excel version.
This formula first locates the first 18 and the first 19 with MATCH, builds a range between those two positions with INDEX, and then counts how many cells in that range equal 5.
Click an empty cell where the count result should appear.
Type =SUMPRODUCT(--(INDEX(D:D,MATCH(18,D:D,0)):INDEX(D:D,MATCH(19,D:D,0))=5)) and press Enter. Replace D:D, 18, 19, and 5 with your actual column, marker values, and target number.
Check the returned number against the column to ensure it counts only the target value between the first 18 and the first 19.
Count Target Values in Multiple Marker Sections with SCAN and LAMBDA
Use this dynamic array formula when you need to count target values in every section between repeated 18 and 19 markers, not just the first pair.
Count a Target Number Between Marker Values in WPS Office Excel
Use WPS Office Excel to count only the occurrences of a specific number that fall between two marker values. The SUMPRODUCT, INDEX, and MATCH formula works in familiar Excel-compatible spreadsheets without manual counting.
- 1. Open your workbook: Launch WPS Office and open the Excel workbook that contains the column with your marker values and target number.
- 2. Select the result cell: Click an empty cell where you want the count result to appear.
- 3. Enter the formula: Type =SUMPRODUCT(--(INDEX(D:D,MATCH(18,D:D,0)):INDEX(D:D,MATCH(19,D:D,0))=5)) and press Enter. Replace D:D, 18, 19, and 5 with your column, marker values, and target number.
- 4. Verify the count: Check the result to ensure it only counts the target number located between the first 18 and the first 19 in the column.
Frequently Asked Questions
Can I count a target value between every 18 and 19 pair, not just the first pair?
Yes. If your version supports LET and SCAN, use the SCAN-based formula: =LET(active,SCAN(0,D:D,LAMBDA(u,v,SWITCH(v,18,1,19,0,u))),SUM(active*(D:D=5))). It resets the marker state at each 18 and 19 and sums target values in active sections.
What should I change if my marker values are in a different column or start at row 2?
Replace D:D with the actual range, such as D2:D100, and replace 18, 19, and 5 with your marker values and target number. Make sure the MATCH range and INDEX range use the same column or range.
Why does COUNTIFS not solve this marker-position problem?
COUNTIFS counts cells that meet value and threshold conditions, but it does not limit the count to the segment between the first occurrence of two marker values.
Does this formula work in WPS Office Spreadsheets?
Yes. The SUMPRODUCT/INDEX/MATCH formula works in WPS Office Spreadsheets and Microsoft Excel. The SCAN formula requires a version that supports LAMBDA and SCAN.
