Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate cohort retention in Excel and Google Sheets
Excel & Google Sheets
=COUNTIF(B2:B6,">0")/COUNTA(A2:A6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| User ID | Active |
| 101 | 1 |
| 102 | 0 |
| 103 | 1 |
| 104 | 1 |
| 105 | 0 |
=COUNTIF(B2:B6,">0")/COUNTA(A2:A6)→0.6
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Prepare your data with user IDs or unique identifiers in one column and their activity status (e.g., 1 for active, 0 for inactive) in another column.
- 2In an empty cell, enter the formula to count active users divided by the total number of users in the cohort.
- 3Format the result as a percentage to easily interpret the retention rate.
Tips
- Ensure your data range matches the formula's references to avoid errors.
- Consider using conditional formatting to highlight retention rates above or below certain thresholds.
- Check for any missing or incorrect data entries that might skew your retention calculations.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What does the formula do?
The formula calculates the percentage of active users in the cohort by dividing the count of active users by the total number of users.
Can I use this formula for multiple cohorts?
Yes, you can adapt the formula for different cohorts by changing the cell references to match each cohort's data range.
What if my data includes dates instead of activity flags?
If your data includes dates, you may need to use additional functions like COUNTIFS to filter users based on specific date criteria.