Statistical
Count consecutive monthly orders: active retention streak across columns
Count how many unbroken trailing months a customer has ordered right up to the latest close using reverse XMATCH(0, (Row > 0)*1, 0, -1).
Find the last inactive zero month from the right to measure current streak!
COUNTIF(B2:G2,">0") counts five active months in [2,0,3,1,4,2]. The trailing streak is four, because February breaks the run ending in June.
Reverse XMATCH finds the rightmost non-active position. Subtracting it from the number of columns gives the trailing streak; if all periods are active, IFNA supplies zero and the result is six.
Arrange completed periods from oldest to newest, with no missing periods. Use numeric nonnegative order counts and explicit zero for no orders. Do not include future months or treat unknown data as zero. A zero in the latest month is inactivity in this definition, not proof that a customer has churned.
| Account | Jan..Jun Orders [B..G] | Last Zero Pos | Current Active Streak |
|---|---|---|---|
| Acme Corp | [2, 0, 3, 1, 4, 2] | Col 2 (Feb) | 6 - 2 = 4 months |
| Northwind | [1, 2, 1, 3, 2, 1] | None (IFNA -> 0) | 6 - 0 = 6 months (Perfect!) |
| Example C | [4, 3, 2, 1, 2, 0] | Col 6 (Jun) | 6 - 6 = 0 months (Inactive!) |
Trailing consecutive active-month streak and total active-month formulas
Change the range to include exactly the completed periods you want to evaluate. The range does not expand automatically when new columns are added.
=COLUMNS(B2:G2) - IFNA(XMATCH(0, (B2:G2 > 0) * 1, 0, -1), 0)Total active months with >= 1 order (non-consecutive)
Standard COUNTIF(B2:G2, ">0") for overall breadth comparison.
=COUNTIF(B2:G2, ">0")Try the example
Use the original values or change them to test your understanding.