Total the amount for each date, on multiple sheets

I ran into this problem in accounting. A workbook was being generated by the general ledger that created separate sheets for each ATM, with dates and deposits.  There were a total of 19 ATMs. Individual dates for each sheet were in column A and amounts in column C. The dates did not necessarily show up in the same row for each sheet, nor was the same date on all sheets. In addition, the same date may show up more than once on a sheet. A chart was needed showing all deposits in all ATMs for each date.

First, we need a list of all sheets. A small macro did that. Although we only had 19 ATMs, larger institutions could have considerably more and the macro would be a necessity:

Sub listme()
Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
Range(“d2”).Select
For Each wSht In allwShts
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = wSht.Name
Next wSht
Range(“d2”).Select
End Sub

The list of sheets I named MySheets

Mysheets

The dates :

Date and no sums

Enter in cell B4 and copy down the following formula: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&MySheets&”‘!a1:a500”),A4,INDIRECT(“‘”&MySheets&”‘!c1:c500”)))

INDIRECT – This function turns a string into a reference. For example: indirect(“‘”&MySheets&”‘!a1:a500”), refers to the range a1:a500 on the sheet referenced by MySheets.  MySheets is a dynamic range of all the sheets to be evaluated.

SUMIF – This function uses the indirect references and sums each sheet based upon the criteria that it match a certain date. However, when used on its own, sumif will only report on the first sheet created. The function works on a range, but not an array.

SUMPRODUCT–This function  allows the SUMIF function to be performed on all 19 worksheets, by creating an array of summations for each sheet that is  then totaled.

The answers are than charted:

Dates and sumsDepost chart

Download “Sum_all_values_in_each_sheet_for_each_date

http://www.pistulka.com/Excel_Shared/

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

Your email address will not be published. Required fields are marked *