Bill only the memberships that are actually still active
SUMIF and COUNTIFS only touch the rows that satisfy every condition you give them, so a cancelled or paused membership can sit in the sheet without ever counting toward the active numbers.
Task:
You run billing for a neighbourhood gym. The membership sheet below lists every signup on the books, whatever its current status, across three plans. In D10, count how many Premium memberships are actually Active. In D11, total the monthly revenue coming in from every Active membership, of any plan. In D12, find the average monthly fee paid by Active Family plan members.
Learning Objectives:
- Combine two conditions in COUNTIFS so a row only counts when every one of them matches
- Use SUMIF's condition column and sum column independently, rather than assuming they're the same column
- Average a subset of rows with AVERAGEIFS instead of averaging everything and correcting the answer by hand
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Member | Plan | Status | Monthly Fee |
| 2 | Aisha Bello | Premium | Active | 89 |
| 3 | Tom Walsh | Basic | Active | 39 |
| 4 | Mara Chen | Family | Active | 129 |
| 5 | Diego Ruiz | Premium | Cancelled | 89 |
| 6 | Sade Okafor | Family | Paused | 129 |
| 7 | Priya Nair | Premium | Active | 89 |
| 8 | Liu Wei | Premium | Active | 79 |
| 9 | Tunde Bakare | Family | Active | 119 |
| 10 | Active Premium members | |||
| 11 | Total monthly revenue, Active members only | |||
| 12 | Average fee, Active Family members |
What this exercise teaches (contains the answer)
COUNTIFS(B2:B9,"Premium",C2:C9,"Active") only counts a row when Plan and Status both match on that same row, which is why Diego Ruiz's cancelled Premium membership never inflates the count even though his plan is right — three Premium rows satisfy both conditions, not four. SUMIF(C2:C9,"Active",D2:D9) tests Status in one column but adds from another, so it can total every active member's fee regardless of plan without needing a second condition at all. AVERAGEIFS(D2:D9,B2:B9,"Family",C2:C9,"Active") narrows to just Mara Chen and Tunde Bakare — Sade Okafor's Family membership is excluded by its Paused status even though her plan matches — so the $124 average reflects only the two Family members actually paying right now, not the $125.67 you'd get by averaging all three Family rows regardless of status.