monthly deposits

Hi All,

I have a spreadsheet that currently lists all of my clients(by account number) and all of their deposits made to my bank for the past 2 years. However, the clients are listed everytime they made a deposit, so if you can imagine, I have in column A1 many duplicated account numbers. (see attached)

My goal is to use this dataset and create a new worksheet that lists only each account number once in column A1, and then list all the months from Jan '07 to Dec 08 from column B1 to Y1. If account number 518500(See attached example) made a deposit in 11/07 for $329, 12/07 for $360, 12/07 for $50 , 12/07 for $600 (3 deposits in December), and so on, how do I get excel to plot the total deposits per month onto the new worksheet with the month/year listed from B1 to Y1? I'm okay with blanks in the months a certain client didn't make a deposit

Of course, as much automation as possible is ideal because I have over 13000 entries!

Thank You very much!

Frank

nice goal! please post an excel example not an image.

Hi,

on another sheet (Sheet2) in your work book, create a unique list of clients in column A.

In the first row enter the dates as the first of the month, i.e. 1-Jan-07, 1-Feb-07, etc.

Then in cell B2 enter the following formula

=SUMPRODUCT(($A2=Sheet1!$A$2:$A$30)*(Sheet2!B$1=DATE(YEAR(Sheet1!$B$2:$B$30),MONTH(Sheet1!$B$2:$B$30),1))*Sheet1!$C$2:$C$30)

Copy down and across.

See attached file for an example.

SUMPRODUCT is very powerful and not very well known. I remember my first WOW effect, too.

If you liked my solution, you can add to my reputation by clicking on the scale icon in the blue bar above my post.

cheers

As I'm sure teylyn would agree SUMPRODUCTs though elegant are inefficient and ill advised when used in large quantities. Given in this instance they are being used to essentially ape the output of a Pivot Table why not use a PT ? It would in truth be more efficient Long Term and easier to adapt.

If you are unwilling to use PT's then I would still advise against Sumproduct, you would be best served creating a concatenation column on your source sheet, eg:

Sheet1!D2: =$A2&":"&$B2-DAY($B2)+1
copied down for all rows

You can then dispense with need for Sumproduct and revert to the far more efficient SUMIF, eg:

results!B2: =SUMIF(Sheet1!$D:$D,$A2&":"&B$1,Sheet1!$C:$C)
Where A2 holds account and B1 the month of interest

I have provided an example of both methods in the attached file.