Function Problems

Summarize Monthly Values and Charity Donations Across Excel Worksheets

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Create a summary sheet that collects monthly worksheet values and totals annual donations by charity.

Summarize Monthly Values and Charity Donations Across Excel Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the helper sheet

Add a worksheet named Helper. Enter Charity in A1 and Donation in B1.

2
Link the monthly records

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.

3
Create the unique charity list

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.

4
Calculate annual totals

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.

Build an annual charity summary with a helper sheet
Check the source data: Donation amounts must be numeric. If all charity cells are blank, FILTER may return a calculation error until a charity is entered.
Summarize donations in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to load the workbook containing the monthly sheets.
  2. 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. 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. 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.
Combine monthly charity records into one annual summaryCalculate donation totals with SUMIFOpen and save Microsoft Excel XLSX workbooksUse familiar spreadsheet tools to organize data
microsoft office alternative - wps office

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.