One Payment, Multiple Dates, summation

[GQuinn]

My data is organized as follows:

Payment Date Date Date

25.00 5/14/2009 07/14/2009 09/14/2009
50.00 05/15/2009 09/15/2009 01/15/2010


is there any way that i could sum the total payments made per month and year across the entire sheet?

the payment is assessed every time a date occurs. Therefore, I would pay 25.00 on 5/14/2009, 07/14/2009 and 09/14/2009.
I also pay 50.00 on 5/15/2009, 9/15/2009 and 01/15/2010.

So the total for May 2009 would be $75.00, July 2009 would be 25.00, September 2009 75.00, January 2010, $50.00

Is there a function, preferably not a long macro, that could do this for me?

Worksheet attached, let me know if further clarification needed

[TMS]

Cell B7:

Please Login or Register  to view this content.

And drag down

Regards

[GQuinn]

thank you!
i really appreciate it!
However, my actual dataset is actually thousands of rows long. Would there possibly be another way to accomplish this?

[TMS]

A realistic sample dataset is always beneficial in getting the result you are looking for :-)

Please Login or Register  to view this content.

Note that the "dates" in your original example are not "true" dates as they mostly have leading and trailing spaces. You'll need to address that or this formula will return a #VALUE! error.

See the attached spreadsheet. There is one sheet showing the working formula and another with it broken by one value with spaces in it.

Regards

[LenaJ887]

Thanks!
(and yes, next time i will include a more realistic example!)

How would I make my dates into real dates? Sorry for all of the questions, I really appreciate all of your help!

[TMS]

GQuinn/LenaJ887: are you leading a double life?

If there are truly thousands of rows, it's not going to be a five minute job.

The first problem is that there are leading and trailing spaces. Where does your data come from? A web site maybe? You should be able to get rid of these spaces using TRIM and/or CLEAN.

However, even then, what is left isn't recognised as a date ... at least on my machine (in the UK).

The dates could be dis-assembled and re-assembled with the following formula, if necessary:

Please Login or Register  to view this content.

Drag across and down, as necessary.

Copy and Paste Special | Values over the original dates and format as date: mm/dd/yyyy, I guess.

Regards