Tabellab

Donation Log: Totals by Campaign with SUMIF and COUNTIF

Lesson 1 of 20Lab

All lessons
  1. Donation Log: Totals by Campaign with SUMIF and COUNTIFLabFree
  2. Membership Dues Tracker: Who Has Paid and What Is Still OpenLab Locked
  3. Annual Budget vs Actual: Variances and Overspent LinesLab Locked
  4. Restricted vs Unrestricted Funds: Who Owns Which DollarLab Locked
  5. Grant Spend-Down: Remaining Budget and Spending PaceLab Locked
  6. Fundraising Event: Gross Income, Costs and Net ResultLab Locked
  7. Bank Reconciliation of the Club AccountLab Locked
  8. Volunteer Expense Claims: Receipts, Approval Limits and PaymentLab Locked
  9. Cash Flow for the Year: Monthly Balances and the Tight MonthLab Locked
  10. Program vs Admin Costs: Allocating Expenses and the Program RatioLab Locked
  11. Volunteer Hours and In-Kind Gifts: Showing Support Beyond CashLab Locked
  12. Treasurer’s Report for the Board: Year-on-Year Summary and ChartLab Locked
  13. Monthly Budget Phasing: Budget to Date, Variance to Date and a Full-Year ForecastLab Locked
  14. Pledges & Promised Gifts: Outstanding Amounts, Days Overdue and an Ageing TableLab Locked
  15. Petty Cash & Event Float: Counting the Box, Over/Short and the Top-UpLab Locked
  16. Equipment Register: Straight-Line Depreciation, Book Value and This Year's ChargeLab Locked
  17. Event Planning: Break-Even Guests and a Price × Guests Scenario TableLab Locked
  18. Reserves Policy: Free Reserves, Months of Spending Covered and the Target RangeLab Locked
  19. Before the Examiner: Duplicate Payments and Missing Receipt NumbersLab Locked
  20. Final Project – Year-End Statement by Fund with Reserves Check and ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Turn the log into a table with Ctrl+T: new gifts typed below it are included in SUMIF and COUNTIF automatically.
Loading the spreadsheet…

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.

RowDonor (A)Campaign (B)Amount (C)
2A. PatelSpring Appeal50.00
3Green Leaf BakerySeed Fund250.00
4M. OkaforSpring Appeal25.00
5J. NovakYear-End100.00
6R. SilvaSeed Fund75.00
7Hillcrest RotaryYear-End500.00
8L. ChenSpring Appeal40.00
9T. BrennanYear-End60.00
10K. AhmedSeed Fund30.00
11S. MoreauSpring Appeal120.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.

CellWhat it answersFormulaResult
F2Total raised by the Spring Appeal=SUMIF($B$2:$B$11,E2,$C$2:$C$11)235.00
F3Total raised by the Seed Fund=SUMIF($B$2:$B$11,E3,$C$2:$C$11)355.00
F4Total raised by the Year-End appeal=SUMIF($B$2:$B$11,E4,$C$2:$C$11)660.00
G2Number of Spring Appeal gifts=COUNTIF($B$2:$B$11,E2)4
G3Number of Seed Fund gifts=COUNTIF($B$2:$B$11,E3)3
G4Number of Year-End gifts=COUNTIF($B$2:$B$11,E4)3
C13Total donations of the year=SUM(C2:C11)1,250.00
C14Average 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.

Brookfield Community Garden: donations by campaign0100200300400500600700235Spring Appeal4 gifts, average 58.75355Seed Fund3 gifts, average 118.33660Year-End3 gifts, average 220.00USD
Figure 1. The summary block as a chart: the Year-End appeal raised the most, although the Spring Appeal brought in the most gifts.

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.

One large gift pulls the average up0100200300400500Hillcrest Rotary 500average 125.00median 67.50Spring AppealSeed FundYear-EndWithout the 500 gift the average of the other nine is 83.33.
Figure 2. Each dot is one gift. The average (dashed) sits to the right of eight gifts because of the single 500 gift. The median (dotted) better describes a typical donor.

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. In G2 enter =COUNTIF($B$2:$B$11,E2) the same way.
  7. 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.
  8. 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.
  9. Format the amounts as currency with Ctrl+Shift+4 on Windows or Home → Number Format → Currency.
  10. For the board, select E1:F4 and choose Insert → Charts → Clustered Column. Add a title such as Donations by campaign.
  11. 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.
Once the log is an Excel table named, for example, Gifts, you can write =SUMIF(Gifts[Campaign],E2,Gifts[Amount]). Structured references grow with the table, read almost like English and never need dollar signs.

Excel for nonprofit treasurers course: donation log, budget and treasurer report in a spreadsheet
Excel for Nonprofit Treasurers & Volunteers: twenty hands-on labs from the donation log to the treasurer's report for the board.

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. COUNT counts numbers, COUNTA counts non-empty cells, COUNTIF counts 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.
Rules on donation receipts, tax-deductibility, record keeping and reporting differ from country to country and between types of organisation. This lesson teaches the spreadsheet, not the law. Ask your regulator, your umbrella organisation or an accountant what applies to you.

Quick reference: functions and shortcuts in this lesson

Function / shortcutExampleUse
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 tableCtrl+TRanges grow with new gifts
Lock referenceF4B2:B11 becomes $B$2:$B$11
AutoSumAlt+=Inserts SUM of the cells above
Fill downCtrl+DCopies 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.

  1. Membership Dues Tracker: Who Has Paid and What Is Still Open
  2. Annual Budget vs Actual: Variances and Overspent Lines
  3. Restricted vs Unrestricted Funds: Who Owns Which Dollar
  4. Grant Spend-Down: Remaining Budget and Spending Pace
  5. Fundraising Event: Gross Income, Costs and Net Result
  6. Bank Reconciliation of the Club Account
  7. Volunteer Expense Claims: Receipts, Approval Limits and Payment
  8. Cash Flow for the Year: Monthly Balances and the Tight Month
  9. Program vs Admin Costs: Allocating Expenses and the Program Ratio
  10. Volunteer Hours and In-Kind Gifts: Showing Support Beyond Cash
  11. Treasurer’s Report for the Board: Year-on-Year Summary and Chart
  12. Monthly Budget Phasing: Budget to Date, Variance to Date and a Full-Year Forecast
  13. Pledges & Promised Gifts: Outstanding Amounts, Days Overdue and an Ageing Table
  14. Petty Cash & Event Float: Counting the Box, Over/Short and the Top-Up
  15. Equipment Register: Straight-Line Depreciation, Book Value and This Year's Charge
  16. Event Planning: Break-Even Guests and a Price × Guests Scenario Table
  17. Reserves Policy: Free Reserves, Months of Spending Covered and the Target Range
  18. Before the Examiner: Duplicate Payments and Missing Receipt Numbers
  19. Final Project – Year-End Statement by Fund with Reserves Check and Chart

Buy course — $9.90 See the full course