SaaS cohort retention table: mixed-reference retention % and weighted cohort average

Lock Column B ($B2) and hide the unelapsed bottom-right triangle

In a triangular cohort table, Column B holds initial Month-0 signups while Columns C, D, E track active customers 1, 2, and 3 months later. Newer cohorts have blank cells on the right because Month 2 or Month 3 has not happened yet.

IF(COUNTBLANK(D2:D2)=1, "", ROUND(D2 / $B2, 4)) divides every active count by its own cohort's Month-0 base ($B2) while leaving unelapsed future months blank instead of 0%.

Cohort cell retention % and weighted Month-2 retention

For recorded Month-2 observations, divide their active-customer total by the original bases of those same cohorts. Jan and Feb give 210 / 300 = 70%; the future March observation is excluded.

=IF(COUNTBLANK(D2:D2)=1, "", ROUND(D2 / $B2, 4))

Cohort-size weighted retention across elapsed cohorts only

ISNUMBER includes a recorded zero while excluding future blank cells. SUMPRODUCT applies that mask to the original cohort sizes.

=LET(base, SUMPRODUCT(--ISNUMBER(D2:D4), B2:B4), IF(base=0, "No observed cohort", ROUND(SUM(D2:D4) / base, 4)))

Inputs and interpretation

Use positive numeric original cohort sizes and nonnegative integer active counts belonging to those original cohorts. Blank means not observed, not zero customers. Do not include later signups in an earlier cohort. A filled cell alone does not prove that the reporting period is complete; establish the observation cutoff explicitly.

Further reading

• Microsoft Support — SUMIF function