Challenge Scenario
In fitness centers, gym clubs, and subscription services, member engagement is monitored by counting workout check-ins across weekly training sessions.
Members who achieve at least 10 workout visits within a 4-week billing cycle are rewarded with "ACTIVE" loyalty status, unlocking renewal perks and discounts.
As a Gym Membership Coordinator, you are reviewing 4 member attendance logs:
- Total Visits (Column F): Sum workout check-ins across Weeks 1 to 4 (
B2:E2) using the SUM function.
- Active Status (Column G): If Total Visits is 10 or more (
F2 >= 10), assign "ACTIVE"; otherwise assign "INACTIVE".
- Total Club Visits (Cell F7): Calculate grand total gym visits across all members using
SUM(F2:F5).
- Active Members Count (Cell G8): Count how many members earned the
"ACTIVE" status using COUNTIF(G2:G5, "ACTIVE").
Your Sheet Layout
The gym membership attendance tracker is organized into clear operational sections:
- Weekly Visit Logs (Columns A to E): Shows member names and raw check-in counts for Week 1, Week 2, Week 3, and Week 4.
- Monthly Totals & Status (Columns F & G): Calculates 4-week visit sums and evaluates loyalty status thresholds.
- Club Facility Summary (Rows 7 & 8): Aggregates total monthly turnstile check-ins and active member counts.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:E5 |
Member Name, Weeks 1-4 |
Attendance Logs |
Weekly check-in logs |
Read-only weekly member check-in records. |
F2:F5 |
Total Visits |
Calculation |
=SUM(B2:E2) |
Adds up visits across 4 weeks for each member. |
G2:G5 |
Active Status |
Status Tier |
=IF(F2 >= 10, "ACTIVE", "INACTIVE") |
Flags members with 10 or more visits as ACTIVE. |
F7 |
TOTAL CLUB VISITS |
Facility Summary |
=SUM(F2:F5) |
Calculates total visits across all club members (36). |
G8 |
ACTIVE MEMBERS |
Facility Summary |
=COUNTIF(G2:G5, "ACTIVE") |
Counts members achieving active status (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column F (F2:F5), calculate Total Visits across all 4 weeks using SUM(B2:E2).
2
In Column G (G2:G5), assign Active Status using =IF(F2>=10, "ACTIVE", "INACTIVE").
3
In cell F7, calculate total club visits using SUM(F2:F5).
4
In cell G8, count active members using COUNTIF(G2:G5, "ACTIVE").
Solution & Formula Breakdown
Let's examine how weekly attendance sums and threshold evaluations work:
1. Summing 4-Week Visits with SUM (F2:F5)
=SUM(B2:E2)
The SUM function calculates horizontal totals for each member:
- John Wick (Row 2):
4 + 3 + 5 + 4 = 16 visits → 16.
- Bruce Wayne (Row 3):
1 + 0 + 2 + 1 = 4 visits → 4.
- Clark Kent (Row 4):
3 + 3 + 4 + 2 = 12 visits → 12.
- Tony Stark (Row 5):
2 + 1 + 0 + 1 = 4 visits → 4.
2. Evaluating Loyalty Status with IF (G2:G5)
=IF(F2 >= 10, "ACTIVE", "INACTIVE")
If Total Visits is 10 or greater, output "ACTIVE"; otherwise output "INACTIVE".
3. Facility Summary Rollups (F7 & G8)
- Total Club Visits (Cell F7):
=SUM(F2:F5) adds 16 + 4 + 12 + 4 = 36 total visits.
- Active Members (Cell G8):
=COUNTIF(G2:G5, "ACTIVE") returns 2 active members.
When summing contiguous rows, specify the full horizontal range B2:E2 rather than adding cells individually (=B2+C2+D2+E2). Range notation is cleaner, faster, and less error-prone!