You just became the treasurer. Now what?
Somebody had to say yes at the annual meeting, and this year it was you. The previous treasurer hands over a bank login, a shoebox of receipts and a spreadsheet with a long list of donations, and within a week the board asks its first question: how much did each appeal raise, and how many people gave? If the honest answer is "give me an evening with a calculator", this free lesson is for you. With two functions, SUMIF and COUNTIF, a donation list answers that question in seconds and keeps answering it every time a new gift arrives.
This lesson is written for volunteer treasurers of charities, clubs, sports associations, parent-teacher groups, garden societies and faith communities, and for staff in small nonprofits who look after the money alongside another job. You do not need a finance background. If you can type into a cell, you can do this. We follow the fictional Brookfield Community Garden Association and the same ten gifts you see in the lab above, so you can check every result in the live spreadsheet. All names and amounts are invented for teaching.
The core concept: one list, many answers
The most important habit of a good treasurer is not a clever formula. It is keeping one donation log: a single table with one row per gift and the same columns every time. At minimum that is the donor, the campaign or appeal the gift answered, and the amount. In practice you will soon add the date, the payment method and whether a receipt or thank-you letter went out.
Once the data lives in one list, every report becomes a question you ask that list: How much did the Spring Appeal raise? How many gifts came in for the Seed Fund? What was the average gift? You never type a total by hand, because a hand-typed total is out of date the moment the next envelope arrives, and nobody can tell how it was calculated. A formula that reads the list is always current and can be checked by anybody on the board.
Two functions do most of the work:
- SUMIF adds the amounts of all rows that meet one condition.
=SUMIF(range, criteria, sum_range)reads: look in range for criteria, and wherever you find it, add the number in the same row of sum_range. - COUNTIF counts the rows that meet a condition.
=COUNTIF(range, criteria)tells you how many gifts a campaign brought in.
Together with SUM for the year's total and AVERAGE for the typical gift, these four functions give the board the summary it wants. Later in the course you will meet their bigger siblings, SUMIFS and COUNTIFS, which accept several conditions at once, for example one campaign and one month.
Worked example: the Brookfield donation log
Here is the log from the lab: ten gifts in rows 2 to 11, with the donor in column A, the campaign in column B and the amount in column C.
| Row | Donor (A) | Campaign (B) | Amount (C) |
|---|---|---|---|
| 2 | A. Patel | Spring Appeal | 50.00 |
| 3 | Green Leaf Bakery | Seed Fund | 250.00 |
| 4 | M. Okafor | Spring Appeal | 25.00 |
| 5 | J. Novak | Year-End | 100.00 |
| 6 | R. Silva | Seed Fund | 75.00 |
| 7 | Hillcrest Rotary | Year-End | 500.00 |
| 8 | L. Chen | Spring Appeal | 40.00 |
| 9 | T. Brennan | Year-End | 60.00 |
| 10 | K. Ahmed | Seed Fund | 30.00 |
| 11 | S. Moreau | Spring Appeal | 120.00 |
Next to the log, in columns E to G, sits a small summary block with one row per campaign. The campaign names in E2:E4 are typed exactly as they appear in the log. The formulas below fill the rest. Each formula is written once in row 2 and copied down, which is why the ranges are locked with dollar signs.
| Cell | What it answers | Formula | Result |
|---|---|---|---|
| F2 | Total raised by the Spring Appeal | =SUMIF($B$2:$B$11,E2,$C$2:$C$11) | 235.00 |
| F3 | Total raised by the Seed Fund | =SUMIF($B$2:$B$11,E3,$C$2:$C$11) | 355.00 |
| F4 | Total raised by the Year-End appeal | =SUMIF($B$2:$B$11,E4,$C$2:$C$11) | 660.00 |
| G2 | Number of Spring Appeal gifts | =COUNTIF($B$2:$B$11,E2) | 4 |
| G3 | Number of Seed Fund gifts | =COUNTIF($B$2:$B$11,E3) | 3 |
| G4 | Number of Year-End gifts | =COUNTIF($B$2:$B$11,E4) | 3 |
| C13 | Total donations of the year | =SUM(C2:C11) | 1,250.00 |
| C14 | Average gift | =AVERAGE(C2:C11) | 125.00 |
Two quick checks prove the summary is complete. The three campaign totals add up to 235 + 355 + 660 = 1,250, the same as the grand total in C13. The gift counts add up to 4 + 3 + 3 = 10, the number of rows in the log. If either check fails, a gift carries a campaign name that is not in your summary list, typically a typo or a stray space. Many treasurers put both checks on the sheet permanently: =SUM(F2:F4)-C13 should be 0 and =SUM(G2:G4)-COUNT(C2:C11) should be 0 too.
The chart already tells a story the bare total would hide. The Spring Appeal reached the most people, four donors, but with small gifts averaging 58.75. The Year-End appeal brought in more than half of the year's money from only three gifts, averaging 220.00. For next year, that difference matters: the spring letter is good at reaching new supporters, the year-end letter is where the larger gifts come from.
Why the average gift needs a second look
The average gift of 125.00 sounds encouraging, but look at the list: eight of the ten gifts are smaller than 125. The average is pulled up by one generous gift of 500 from the Hillcrest Rotary club. Without it, the other nine gifts average 750 ÷ 9 = 83.33. This is not an error in Excel. It is simply what an average does when one value is far larger than the rest.
For that reason it helps to show the median next to the average: =MEDIAN(C2:C11) returns the middle value of the sorted list, here (60 + 75) ÷ 2 = 67.50. When the board sets the target for the next appeal, "a typical gift is around 65 to 85, and one larger gift made the difference" is far more useful than "our average gift is 125". An average per campaign comes from AVERAGEIF, for example =AVERAGEIF($B$2:$B$11,E2,$C$2:$C$11) for the Spring Appeal, or simply =F2/G2 once the total and count exist.
Step by step: build the donation summary in Excel
You can follow these steps in Excel for Windows or Mac, and almost identically in Google Sheets or LibreOffice Calc.
- Type the headers Donor, Campaign and Amount in A1:C1 and enter one gift per row below. Never merge cells or leave blank rows inside the list.
- Click any cell in the list and press Ctrl+T (Mac: Cmd+T) to turn it into an Excel table. Confirm that My table has headers is ticked. New gifts typed directly below the table become part of it automatically.
- To stop spelling variations, select the Campaign column and use Data → Data Validation → Allow: List with the source
=$E$2:$E$4. Now each new gift picks its campaign from a drop-down. - Type the campaign names in E2:E4. In Excel 365 you can let Excel list them for you instead:
=UNIQUE(B2:B11)in E2 spills every distinct campaign. - In F2 type
=SUMIF(, select B2:B11 and press F4 to make it$B$2:$B$11, type a comma, click E2, type a comma, select C2:C11 and press F4 again. Close the bracket and press Enter. - In G2 enter
=COUNTIF($B$2:$B$11,E2)the same way. - Select F2:G2 and double-click the fill handle (the small square at the bottom-right of the selection) to copy both formulas down to row 4. Or select F2:G4 and press Ctrl+D.
- In C13 press Alt+= (Mac: Cmd+Shift+T) for AutoSum and check that the range is C2:C11. In C14 type
=AVERAGE(C2:C11), and if you like add=MEDIAN(C2:C11)below it. - Format the amounts as currency with Ctrl+Shift+4 on Windows or Home → Number Format → Currency.
- For the board, select E1:F4 and choose Insert → Charts → Clustered Column. Add a title such as Donations by campaign.
- Add the two control checks from the worked example and press Ctrl+` to display all formulas once before you send the file, so you can see that no total was typed by hand.
=SUMIF(Gifts[Campaign],E2,Gifts[Amount]). Structured references grow with the table, read almost like English and never need dollar signs.
Common mistakes and how to spot them
Most wrong donation totals come from a handful of causes. Here is how to recognise each one.
- Ranges that move when you copy. Without dollar signs,
=SUMIF(B2:B11,E2,C2:C11)becomes=SUMIF(B4:B13,E4,C4:C13)two rows down and misses the first gifts. Symptom: the lower campaigns show too little and the check against C13 fails. Fix: press F4 to lock the ranges. - Spelling and stray spaces. "Year End", "Year-end " and "Year-End" are three different campaigns to SUMIF. Symptom: the campaign totals add up to less than the grand total. Fix: a drop-down list, or clean old data with
=TRIM(B2). Note that SUMIF itself ignores upper and lower case. - Numbers stored as text. Amounts pasted from a bank export or an online giving platform sometimes arrive as text, left-aligned with a small green triangle. SUM and SUMIF skip them silently. Fix: select them and use the warning icon's Convert to Number, or Data → Text to Columns → Finish.
- Mismatched range sizes.
=SUMIF($B$2:$B$11,E2,$C$2:$C$10)uses a sum range one row shorter. Excel quietly resizes it to match the criteria range rather than reporting an error, which makes the mistake easy to overlook in larger sheets. Keep both ranges identical. - Counting the wrong thing.
COUNTcounts numbers,COUNTAcounts non-empty cells,COUNTIFcounts cells meeting a condition. Counting donors with COUNTA on the donor column also counts a heading or a note typed in that column. - Mixing pledges and gifts. A promise to give is not money in the bank. Keep pledges in a separate column or sheet so the donation total shows money actually received.
- Reporting only the average. As Figure 2 shows, one large gift can make the average misleading. Show the median or the full list alongside it.
Quick reference: functions and shortcuts in this lesson
| Function / shortcut | Example | Use |
|---|---|---|
| SUMIF | =SUMIF($B$2:$B$11,E2,$C$2:$C$11) | Total of one campaign |
| COUNTIF | =COUNTIF($B$2:$B$11,E2) | Number of gifts for one campaign |
| SUM | =SUM(C2:C11) | Total donations |
| AVERAGE | =AVERAGE(C2:C11) | Average gift |
| MEDIAN | =MEDIAN(C2:C11) | Typical gift, not distorted by one large gift |
| AVERAGEIF | =AVERAGEIF($B$2:$B$11,E2,$C$2:$C$11) | Average gift of one campaign |
| SUMIFS / COUNTIFS | =SUMIFS(C:C,B:B,E2,D:D,">="&H1) | Several conditions, for example campaign and date |
| UNIQUE (Excel 365) | =UNIQUE(B2:B11) | Lists each campaign once |
| Create table | Ctrl+T | Ranges grow with new gifts |
| Lock reference | F4 | B2:B11 becomes $B$2:$B$11 |
| AutoSum | Alt+= | Inserts SUM of the cells above |
| Fill down | Ctrl+D | Copies the top formula into the selection |
Real-world uses
The same log and the same four functions cover many everyday treasurer tasks:
- Reporting an appeal to the board. The day after a mailing closes, the summary block shows money raised and number of gifts per campaign, ready to paste into the minutes.
- Planning the next appeal. Comparing average and median gifts per campaign helps the fundraising volunteers set a realistic target and decide which letter to send to whom.
- Tracking money for a specific purpose. Gifts to a named fund, like Brookfield's Seed Fund, may only be used for that purpose. A SUMIF on the campaign column shows at any time how much was given for it.
- Thanking donors. Add a column "Thanked" with Yes or No and
=COUNTIF(D2:D11,"No")shows how many thank-you letters are still to be written. - Handing over to the next treasurer. A log with formulas instead of typed totals is self-explaining. Your successor can see exactly how every figure was produced.
What you will build in the rest of this course
This first lesson is free. The other nineteen lessons are part of the full course, a one-time purchase of $9.90, and follow the Brookfield garden through a whole treasurer's year, each with its own live lab and instant feedback:
Membership Dues Tracker: Who Has Paid and What Is Still Open. You look up each member's fee from a rate table, count who has not paid and see the money still outstanding, so reminders go to the right people.
Annual Budget vs Actual: Variances and Overspent Lines. You compare the year's spending with the budget the members approved and highlight every line that went over, the table every annual meeting asks for.
Restricted vs Unrestricted Funds: Who Owns Which Dollar. You roll each fund forward from opening to closing balance and split the bank balance into money you may spend freely and money promised to a purpose.
Grant Spend-Down: Remaining Budget and Spending Pace. Seven months into a twelve-month grant, you check what is left, how fast it is being spent and what must be spent each month to use it in time.
Fundraising Event: Gross Income, Costs and Net Result. You work out what a plant sale and supper really raised once every cost is deducted, which is often a surprise.
Bank Reconciliation of the Club Account. The bank statement and the cash book disagree. You reconcile them and prove that no money is missing, a skill that makes the annual audit much calmer.
Volunteer Expense Claims: Receipts, Approval Limits and Payment. Excel decides which claims can be paid now, which need a second signature and which wait for a receipt.
Cash Flow for the Year: Monthly Balances and the Tight Month. You carry the bank balance through twelve months of receipts and payments and find the month when money runs tightest, before it happens.
Program vs Admin Costs: Allocating Expenses and the Program Ratio. You split each expense between programs and administration and calculate the program ratio for the annual report.
Volunteer Hours and In-Kind Gifts: Showing Support Beyond Cash. You total volunteer hours, put a value on them and add donated goods, so the report shows all the support the organisation received.
Treasurer's Report for the Board: Year-on-Year Summary and Chart. You finish with a one-page report for the annual meeting: income this year against last year, the surplus, a chart and a highlight on falling income.
Summary
A treasurer's numbers are only as good as the list behind them. Keep one donation log with one row per gift, then let SUMIF total each campaign, COUNTIF count its gifts, SUM give the year's total and AVERAGE (with MEDIAN next to it) describe the typical gift. Lock the ranges with dollar signs, keep campaign names consistent and add the two control checks. In the Brookfield example, ten gifts bring in 1,250, with the Year-End appeal raising 660 from just three gifts.
Now scroll up to the lab and try it yourself: fill F2:G4 with SUMIF and COUNTIF, add the total and the average gift, and watch the feedback tell you at once whether your ranges are locked. It takes about fifteen minutes, and afterwards you will never total a donation list by hand again.
Frequently asked questions
How do I total donations by campaign in Excel?
Use SUMIF with the campaign column as the range, the campaign name as the criteria and the amount column as the sum range, for example =SUMIF($B$2:$B$11,E2,$C$2:$C$11). Copy it down for each campaign.
How do I count the number of gifts per campaign?
Use COUNTIF on the campaign column: =COUNTIF($B$2:$B$11,E2). It counts every row whose campaign matches the name in E2.
Why does my SUMIF return 0 or too little?
Usually the campaign name differs slightly, for example an extra space or a different spelling, or the amounts are stored as text. Use a drop-down list for campaigns, TRIM for old data and convert text amounts to numbers.
What is the difference between SUMIF and SUMIFS?
SUMIF accepts one condition, SUMIFS accepts several, such as one campaign and a date range. In SUMIFS the sum range comes first: =SUMIFS(C:C,B:B,"Year-End",D:D,">="&H1).
Should I report the average or the median gift?
Report both if you can. The average is easy to understand but one large gift can pull it up a lot, while the median shows what a typical donor gave.
What is the best way to keep a donation log in Excel?
Keep one table with one row per gift and fixed columns such as date, donor, campaign, amount and receipt status, turn it into an Excel table with Ctrl+T and calculate every total with formulas instead of typing it.
Keep going: Excel for Nonprofit Treasurers & Volunteers: Donations, Budgets & Reports
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Membership Dues Tracker: Who Has Paid and What Is Still Open
- Annual Budget vs Actual: Variances and Overspent Lines
- Restricted vs Unrestricted Funds: Who Owns Which Dollar
- Grant Spend-Down: Remaining Budget and Spending Pace
- Fundraising Event: Gross Income, Costs and Net Result
- Bank Reconciliation of the Club Account
- Volunteer Expense Claims: Receipts, Approval Limits and Payment
- Cash Flow for the Year: Monthly Balances and the Tight Month
- Program vs Admin Costs: Allocating Expenses and the Program Ratio
- Volunteer Hours and In-Kind Gifts: Showing Support Beyond Cash
- Treasurer’s Report for the Board: Year-on-Year Summary and Chart
- Monthly Budget Phasing: Budget to Date, Variance to Date and a Full-Year Forecast
- Pledges & Promised Gifts: Outstanding Amounts, Days Overdue and an Ageing Table
- Petty Cash & Event Float: Counting the Box, Over/Short and the Top-Up
- Equipment Register: Straight-Line Depreciation, Book Value and This Year's Charge
- Event Planning: Break-Even Guests and a Price × Guests Scenario Table
- Reserves Policy: Free Reserves, Months of Spending Covered and the Target Range
- Before the Examiner: Duplicate Payments and Missing Receipt Numbers
- Final Project – Year-End Statement by Fund with Reserves Check and Chart