One Payment, Multiple Dates, summation

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

Cell B7:

=SUMPRODUCT((MONTH($B$2:$D$2)=MONTH($A7))*$A$2)+SUMPRODUCT((MONTH($B$3:$D$3)=MONTH($A7))*$A$3)

And drag down

Regards

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?

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

=SUMPRODUCT((($B$2:$D$5<>"")*(MONTH($B$2:$D$5)=MONTH($A9))*($A$2:$A$5)))

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

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!

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:

=IF(ISERROR(DATEVALUE(MID(TRIM(B2),FIND("/",TRIM(B2))+1,2)&"/"&LEFT(TRIM(B2),FIND("/",TRIM(B2))-1)&"/"&RIGHT(TRIM(B2),4))),TRIM(B2),DATEVALUE(MID(TRIM(B2),FIND("/",TRIM(B2))+1,2)&"/"&LEFT(TRIM(B2),FIND("/",TRIM(B2))-1)&"/"&RIGHT(TRIM(B2),4)))

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