sheetsmith

Templates › Clubs and groups

Club Subs & Fixtures

Made for treasurers and secretaries of small clubs.

A workbook for a small club: who has paid their subs, who still owes, and how the season is going on the pitch. The summary carries a chart that redraws itself as you type.

Tabs
4
Columns
25
Fill themselves in
25

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 830 rows.

Members

One row per member, with the subs they owe for the season and what has been paid so far. Room for 150 rows.

ColumnTypeWorked out as
Member Nametext
Categorychoice (list)
Subs Duecurrency
Date Joineddate
Membershipchoice (list)
Contacttext
Paid To DatecurrencyIF(ISBLANK([full_name]), "", SUMIF([Payments!member], [full_name], [Payments!amount]))
Still OwingcurrencyIF(OR(ISBLANK([full_name]), ISBLANK([subs_due])), "", ROUND([subs_due] - SUMIF([Payments!member], [full_name], [Payments!amount]), 2))
Subs StatustextIF(OR(ISBLANK([full_name]), ISBLANK([subs_due])), "", IF(SUMIF([Payments!member], [full_name], [Payments!amount]) >= [subs_due], "Paid", IF(SUMIF([Payments!member], [full_name], [Payments!amount]) > 0, "Part paid", "Unpaid")))

Payments

One row per subs payment received. Pick the member from the dropdown so the Members tab adds it up. Room for 600 rows.

ColumnTypeWorked out as
Date Receiveddate
Memberchoice (from Members › full_name)
Amountcurrency
Methodchoice (list)
Referencetext
MonthtextIF(ISBLANK([payment_date]), "", TEXT([payment_date], "MMM YYYY"))

Fixtures

The season fixture list. Leave the scores blank until a game has been played. Room for 80 rows.

ColumnTypeWorked out as
Datedate
Opponenttext
Home or Awaychoice (list)
Competitionchoice (list)
Our Scorenumber
Their Scorenumber
ResulttextIF(OR(ISBLANK([goals_for]), ISBLANK([goals_against])), "", IF([goals_for] > [goals_against], "Win", IF([goals_for] = [goals_against], "Draw", "Loss")))
Goal DifferencenumberIF(OR(ISBLANK([goals_for]), ISBLANK([goals_against])), "", [goals_for] - [goals_against])
ScorelinetextIF(OR(ISBLANK([goals_for]), ISBLANK([goals_against])), "", [goals_for] & " v " & [goals_against])
Notestext

Summary

The headline answers: subs collected, who still owes and the season record.

MeasureWorked out as
Members on the listCOUNTA([Members!full_name])
Total subs due for the seasonSUM([Members!subs_due])
Total collected so farSUM([Payments!amount])
Still outstandingSUM([Members!balance])
Share of subs collectedIFERROR(SUM([Payments!amount]) / SUM([Members!subs_due]), 0)
Members who still oweCOUNTIF([Members!payment_status], "Unpaid") + COUNTIF([Members!payment_status], "Part paid")
Members fully paid upCOUNTIF([Members!payment_status], "Paid")
Members who have paid nothingCOUNTIF([Members!payment_status], "Unpaid")
Largest amount owed by one memberIFERROR(MAX([Members!balance]), 0)
Payments received this monthSUMIFS([Payments!amount], [Payments!payment_date], ">=" & EOMONTH(TODAY(), -1) + 1, [Payments!payment_date], "<=" & EOMONTH(TODAY(), 0))
Fixtures playedCOUNT([Fixtures!goals_for])
Fixtures still to playCOUNTA([Fixtures!fixture_date]) - COUNT([Fixtures!goals_for])
Season recordCOUNTIF([Fixtures!result], "Win") & " won, " & COUNTIF([Fixtures!result], "Draw") & " drawn, " & COUNTIF([Fixtures!result], "Loss") & " lost"
Win rateIFERROR(COUNTIF([Fixtures!result], "Win") / COUNT([Fixtures!goals_for]), 0)
Goals scoredSUM([Fixtures!goals_for])
Goals concededSUM([Fixtures!goals_against])
Goal differenceSUM([Fixtures!goals_for]) - SUM([Fixtures!goals_against])
Next fixtureIFERROR(MIN([Fixtures!fixture_date]) + 0, "")