Sum Ticket Sales by Family
As the PTA Treasurer, I need to quickly sum the 'Total Paid' amount for a specific family, 'Smith', for a particular event, the 'Fall Fair'. This helps me reconcile individual family accounts and event revenues.
=SUMIFS(G2:G8, B2:B8, "Smith", C2:C8, "Fall Fair") How it works: The SUMIFS function is perfect for summing values based on multiple conditions. In this scenario, it looks at the 'Total Paid' column (G2:G8) and only includes a value in the sum if two conditions are met: the 'Family Name' in the corresponding row (B2:B8) is 'Smith', AND the 'Event' in the corresponding row (C2:C8) is 'Fall Fair'. This allows the PTA Treasurer to get a precise total for specific family contributions to a particular event.
Data Setup
| Transaction ID | Family Name | Event | Item | Quantity | Unit Price | Total Paid |
|---|---|---|---|---|---|---|
| 1 | Smith | Fall Fair | Tickets | 2 | 10 | 20 |
| 2 | Jones | Spring Gala | Tickets | 3 | 15 | 45 |
| 3 | Smith | Fall Fair | Donation | 1 | 5 | 5 |
| 4 | Davis | Fall Fair | Tickets | 4 | 10 | 40 |
| 5 | Smith | Winter Carnival | Tickets | 1 | 12 | 12 |
| 6 | Jones | Fall Fair | Tickets | 2 | 10 | 20 |
| 7 | Smith | Fall Fair | Raffle | 1 | 2 | 2 |
| 8 | Smith | Spring Gala | Tickets | 2 | 15 | 30 |
Step-by-Step Guide
Open your Excel spreadsheet containing the transaction data.
Identify the column you want to sum (e.g., 'Total Paid' in column G). This will be your 'sum_range'.
Identify the first criteria column (e.g., 'Family Name' in column B). This is your first 'criteria_range'.
Specify the first criteria ('Smith'). Since it's text, enclose it in double quotes.
Identify the second criteria column (e.g., 'Event' in column C). This is your second 'criteria_range'.
Specify the second criteria ('Fall Fair'). Enclose it in double quotes.
Combine these into the SUMIFS function: `=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)`
Enter the complete formula into an empty cell: `=SUMIFS(G2:G8, B2:B8, "Smith", C2:C8, "Fall Fair")` and press Enter.