Conditional Formulas

=IF(OR(A8="2010/2011",A8="2011/2012"),"NOT INSURED",IF((SUMPRODUCT(--(Data!$F$4:$F$1998=$A$1),--(Data!$B$4:$B$1998>=O8),--(Data!$B$4:$B$1998<=P8),Data!$C$4:$C$1998))=0,"Nil",SUMPRODUCT(--(Data!$F$4:$F$1998=$A$1),--(Data!$B$4:$B$1998>=O8),--(Data!$B$4:$B$1998<=P8),Data!$C$4:$C$1998)))

Thanks you very much for your help, but it will show only for particular year of 2010/2011 & 2011/2012. It should be calcuate as per Start Date mentioned in Data Sheet as 01/02/2012.

I'm not sure I understood exactly what you need, but try this one:

=IF(AND(Data!$G$1<41091,OR(A8="2010/2011",A8="2011/2012")),"NOT INSURED",IF((SUMPRODUCT(--(Data!$F$4:$F$1998=$A$1),--(Data!$B$4:$B$1998>=O8),--(Data!$B$4:$B$1998<=P8),Data!$C$4:$C$1998))=0,"Nil",SUMPRODUCT(--(Data!$F$4:$F$1998=$A$1),--(Data!$B$4:$B$1998>=O8),--(Data!$B$4:$B$1998<=P8),Data!$C$4:$C$1998)))

The Start Date =Data!G1, is change every time, and the year of insured also change every time in between five years. so it is not fixed that not insured for the particular period 2010/2011 & 2011/2012. If the Start is 01/02/2013 then the period not insured should be from 2010/2011, 2011/2012, and 2012/2013. it can be one year not insured or it will be inusred hole five years.

So the required conditions is as per Start date mentioned in Data sheet. Not insured year will be decide as per Start Date.

Ok, I got the formula for this,

Formula:

Please Login or Register  to view this content.

Thank you very much for your help