Summarize Monthly Values and Charity Donations Across Excel Worksheets
Question details
Create a summary sheet that collects monthly worksheet values and totals annual donations by charity.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Monthly worksheets contain corresponding values and charity donation records that need to be consolidated for an annual summary.
- Observed behavior
- The desired result is a unique charity list with annual donation totals and links to selected monthly cells.
Check that each monthly sheet uses consistent columns for charity names and donation amounts, and make sure charity names are spelled consistently. The examples below assume names are in column A, amounts are in column B, and each month has up to 100 records starting in row 2.
Build an annual charity summary with a helper sheet
Use this method to combine monthly records and calculate a total for each distinct charity.
The FILTER and UNIQUE formulas require an Excel version that supports dynamic arrays. Adjust the row ranges if a month has more than 100 records.
Add a worksheet named Helper. Enter Charity in A1 and Donation in B1.
In Helper!A2, enter =IF(January!A2="","",January!A2), and in B2 enter =January!B2. Fill both formulas down through row 101. Repeat in separate 100-row blocks for each remaining month, changing the sheet names and starting each block with that month's row 2.
On a new Summary sheet, enter Charity in A1 and Annual Total in B1. In A2, enter =UNIQUE(FILTER(Helper!A2:A1201,Helper!A2:A1201<>"")) for 12 monthly blocks of 100 rows.
In Summary!B2, enter =SUMIF(Helper!$A$2:$A$1201,A2,Helper!$B$2:$B$1201). Fill the formula down beside the charity list, adding formulas for any new charities that appear later.

Reference the same cell on monthly worksheets
Use direct references or a sheet-name list when the summary needs a specific cell from each month.
Create an annual charity summary in WPS Spreadsheets
Use a helper sheet, Remove Duplicates, and SUMIF in WPS Spreadsheets to combine monthly donation records and total them by charity.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load the workbook containing the monthly sheets.
- 2. Combine the records: Create a Helper sheet with Charity and Donation headers. Copy the charity and donation columns from each monthly sheet and use Paste Special > Values to place the records below one another.
- 3. Make a unique list: Copy the charity names from Helper to column A of a Summary sheet. Delete blank entries, select the names, and choose Data > Remove Duplicates.
- 4. Calculate the totals: In Summary!B2, enter =SUMIF(Helper!$A$2:$A$1201,A2,Helper!$B$2:$B$1201), adjusting the range to cover all records. Fill the formula down beside the charity names.

Frequently Asked Questions
How do I reference a worksheet name that contains spaces?
Enclose the sheet name in apostrophes, as in ='Jan Data'!C29.
Why does INDIRECT show a reference error?
Check that the sheet name in the referenced cell exactly matches an existing worksheet and that the formula includes a valid cell reference, such as =INDIRECT("'"&A2&"'!C29").
What if my Excel version does not support FILTER and UNIQUE?
Copy the helper sheet's charity names to the summary, remove blank entries, and use Data > Remove Duplicates. Then use SUMIF beside each remaining name.
Why is a charity's annual total incorrect?
Check that its name is spelled consistently across monthly sheets, that donation amounts are numeric, and that the SUMIF ranges cover every helper-sheet record.
Will the WPS summary update when I change a monthly sheet?
Not if you combined the records using Paste Special > Values. Copy the updated records to the Helper sheet again, then refresh the charity list and totals as needed.




