Count rows in one column if the adjacent column is less than a specific date

[fragelracken]

Hi,

Currently using Excel 2007, but need a formula that works with Excel 2003

I have a spreadsheet with a reference field: H5 "Close Date", and columns as: J: "Payment Date", K: "Principal+Interest" and M: "Interest Only".

I would like to count the numbers of times the columns K and M have entries in them (no blank cells) ONLY if the J Column "Payment Date" is less than or equal to the "Close Date"

Any Help would be greatly appreciated.

ZZ_Loan.Calculator-Update.xls
	F	G	H	I	J	K	L	M	N	O
1			v 1.0		PaymentDate	Principal + Interest	Additional Principal	Interest Only	OtherFees	Interest Fee
2					8/1/2005			416.67
3					9/1/2005			416.48
4	Quarter Close Backward				10/1/2005	438.79
5		Close Date	12/30/2005		11/1/2005	438.79
6		Days of Interest	59		12/15/2005	438.79
7		Beg Principal Balance	$50,000.00		1/1/2006	438.79
8		Paydowns	$89.58
9		End Principal Balance	$49,910.42
10		Net Principle Due	$22.11
TEST

 

[mikerickson]

something like this should work
=SUMPRODUCT(--(K4:K10<>""),--(J4:J10<=H5))

 

[Jonmo1]

Try

=SUMPRODUCT(--(J2:J10<=H5),--(K2:K10&M2:M10<>""))

 

[fragelracken]

=SUMPRODUCT(--(J2:J10<=H5),--(K2:K10&M2:M10<>""))

That works perfectly,

Thank you!