Curly calc -- Lookup? Match?

[SydneyGeek]

Hi, struck a problem that has me losing what hair remains.
Basically, the layout is this:

CashFlow by Half Yr.xls
	A	B	C	D	E	F	G	H
1
2
3	Jun-04	Dec-04	Jun-05	Dec-05	Jun-06	Dec-06	Jun-07	Dec-07
4				100000	50000
5
6
7
8		PmtMonth	PmtDate
9		Jul-05	50000
10		Dec-05	50000
11		Mar-06	50000
12
Sheet1

B8:C11 contain a lookup table of dates and values.
Row 3 contains dates by half-year.
I want to place payments from the lookup table in Row 4, summing where required.
Does anyone have any ideas?

Thanks
Denis

 

[SydneyGeek]

Thanks tactps,
the problem is that not all the dates will match -- they can be any month in the lookup table and only Jun or Dec in the flow. I was looking at comparing $B$9:$B$11 to something like EDATE(C3,-5) to C3, to cover the half-year, and that's the bit that fried the brains.

Denis

 

[Chitosunday]

put this in cell a4
=SUMPRODUCT(($B$9:$B$11<=A3)*$C$9:$C$11)
in cell b4
=SUMPRODUCT(($B$9:$B$11>A3)*($B$9:$B$11<=B3)*$C$9:$C$11)

Then copy cell b4 to succeeding column

 

[eliW]

Hi Denis,

It can be done simply by Pivot Table.

Just add column B and format to Date

Eli

Book1
	A	B	C	D	E	F	G
1
2	month	period	pmt		Sumofpmt
3	Jul-05	12-2005	5000		period	Total
4	Dec-05	12-2005	5000		12-2005	10000
5	Mar-06	06-2006	5000		06-2006	5000
6					GrandTotal	15000
7
Sheet1

 

[SydneyGeek]

Thanks guys,

EliW -- Pivot Table would normally be great but this is part of something where I couldn't change the layout. I hadn't thought of using CEILING and DATEVALUE like that before -- that will come in very handy for another problem I'm working on

ChitoSunday -- spot on!

Thanks again
Denis

 

[Brian from Maui]

put this in cell a4
=SUMPRODUCT(($B$9:$B$11<=A3)*$C$9:$C$11)


No need for Sumproduct,

=SUMIF($B$9:$B$11,"<="&A3,$C$9:$C$11)