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