Curly calc -- Lookup? Match?

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi, struck a problem that has me losing what hair remains.
Basically, the layout is this:
CashFlow by Half Yr.xls
ABCDEFGH
1
2
3Jun-04Dec-04Jun-05Dec-05Jun-06Dec-06Jun-07Dec-07
410000050000
5
6
7
8PmtMonthPmtDate
9Jul-0550000
10Dec-0550000
11Mar-0650000
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Hi Denis,

It can be done simply by Pivot Table.

Just add column B and format to Date

Eli
Book1
ABCDEFG
1
2monthperiodpmtSumofpmt
3Jul-0512-20055000periodTotal
4Dec-0512-2005500012-200510000
5Mar-0606-2006500006-20065000
6GrandTotal15000
7
Sheet1
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top