How to Count Rows When a Later Column Contains NULL in Excel
Question details
The user needs to count 'Shopping' entries in each row only when a later column in that row contains the text 'NULL'; rows without NULL after the target value must not be counted.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table or row range with text values and wants a formula to perform a row-wise conditional count based on the order of two values.
- Observed behavior
- Standard aggregate counting does not consider the left-to-right order of values within each row, so the result must be calculated with dynamic array or lookup formulas.
Confirm that the source range has a consistent column order and that the cells contain the exact text "Shopping" and "NULL" with no extra spaces. If the table has hidden columns, decide whether they should be included in the row-wise search.
Use BYROW with a custom CountBeforeLambda to count qualifying rows
Best for modern Excel and WPS Spreadsheets that support dynamic arrays and LAMBDA. This method calculates each row independently and returns a spilled result.
Define a reusable LAMBDA that accepts a value to count and a stopping value. For each row, it finds the last occurrence of the stopping value, takes only the cells up to that position, and counts how many equal the target value.
Use a named table or a fixed range as the first argument to BYROW. The result is 1 when the target appears before the last stop value; otherwise it is 0.
Open WPS Spreadsheets, click Formulas > Name Manager > New, enter the name CountBeforeLambda, and paste this formula: =LAMBDA(countString,untilString,LAMBDA(line,LET(end,XMATCH(untilString,line,,-1),matches,TAKE(line,,end)=countString,IFERROR(SUM(SIGN(matches)),0))))
Select an empty cell outside the table and enter =BYROW(table,CountBeforeLambda("Shopping","NULL")). Replace table with your actual table name or range, then press Enter.
Confirm that each row returns 1 when "Shopping" occurs before the last "NULL" and returns 0 when the condition is not met.
Use XMATCH to compare the last NULL and Shopping positions in a row
Use this shorter fill-down formula when you work with a fixed row range such as C2:G2 and do not need to define a LAMBDA name.
Count rows with NULL after Shopping directly in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the same Excel-compatible dynamic array functions, BYROW, LAMBDA, and XMATCH, to count row-wise conditions. WPS reads and writes .xlsx files, so you can apply this solution without changing tools or losing format.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel file that contains the table or row range.
- 2. Define the CountBeforeLambda name: Click Formulas > Name Manager > New, name it CountBeforeLambda, and paste the LAMBDA formula shown in the solution above.
- 3. Apply the BYROW formula: Click an empty cell and enter =BYROW(table,CountBeforeLambda("Shopping","NULL")), replacing table with the actual table name or range.
- 4. Review and save the results: Press Enter, confirm each row shows 1 or 0 as expected, then save the workbook in .xlsx format.
Frequently Asked Questions
Does this formula work in every version of Excel or WPS Spreadsheets?
BYROW and LAMBDA require a version that supports dynamic array functions, including Microsoft 365 and recent WPS Office Spreadsheets. In older versions, use the XMATCH row formula if available, or check for updates.
What happens if NULL appears before Shopping in a row?
The row returns 0 because the formula compares the last NULL position with the Shopping position. A NULL located before Shopping does not satisfy the 'later column contains NULL' condition.
Can I use a normal range instead of a named table?
Yes. For BYROW, use a fixed range such as C2:G8 as the first argument. For the shorter method, use the row range formula and fill it down.
Why does my formula return #NAME? in WPS Spreadsheets?
#NAME? usually means the CountBeforeLambda name has not been created, the formula spelling is incorrect, or the installed WPS version does not support LAMBDA. Verify the name in Formulas > Name Manager and update WPS Office if needed.
Can I count a different target value or stopping text?
Yes. Replace "Shopping" and "NULL" in the formula with the target and stopping text you need. The LAMBDA method accepts these as arguments, so no other change is required.
