Complex IF in Range using Dates

[Caren]

Hi,

I need to command that if a date (Cell N6:N15) is after another date (P4) but before another date (Q4) then how many months are between Cell N and Q.
Main problem is that as I would like to drag this formula across only, at the bottom of a table, I do not know how to get it to search up and down column N, where the dates are.
I so far have figured this out:

=IF(AND($N14>P$4,$N14<q$4),(q$4-$n14) 365.25,0)

This only works for one cell in column N.

Then, I would also like the output to add itself to other outputs if there are multiple dates that come true.

Thank you </q$4),(q$4-$n14)>

 

[AlanY]

if i understand correctly, replace $N14

with INDEX($N$6:$N$15,MATCH(LOOKUP(9.99E+100,P6:P15),P6:P15)), thus

Excel 2012
	N	O	P	Q
4			01-May-16	01-Aug-16
5			31-Jul-16	31-Oct-16
6
7
8
9
10
11
12
13
14	01-Jun-16		-11,100
15	31-Oct-16			-74,535
16			0.17	0.00
17
Sheet1

Cell Formulas
Range	Formula
P16	=IF(AND(INDEX($N$6:$N$15,MATCH(LOOKUP(9.99E+100,P6:P15),P6:P15))>P$4,INDEX($N$6:$N$15,MATCH(LOOKUP(9.99E+100,P6:P15),P6:P15))),(Q$4-INDEX($N$6:$N$15,MATCH(LOOKUP(9.99E+100,P6:P15),P6:P15)))/365.25,0)

 

[Caren]

Hi

Thank you for your efforts,

What does 9.99E+100 mean?

I have come up with a new solution

But am still stuck.

As you can see I need excel to perform the function I have highlighted in the address bar of the picture attached.

However, I want excel to know when to perform this function. Which is, when there is a non blank cell. ie. P14.

So I want to say something like this:

In cells P6:P15 are any cells non blank? ie <>""

if so then take the corresponding row of this non blank cell and carry out the formula that I have written according to the correct row. (Take the refurb end date - first date of the quarter)

The formula I have written will only work for that one row in particular.
http://s24.postimg.org/id4cqt4ut/excel.png