Year Function

[sgmpatnaik]

Hello Good Evening


i have small doubt in the Year Function That is


I am trying to calculate the year as a financial year i mean from 01-04-2012 to 31-03-2013 is a one year


for this i am using the formula as like


=SUMPRODUCT(('Stock Receipt and Despatch'!$B$5:$B$14501>=DATE(YEAR($I$5),1,1))*('Stock Receipt and Despatch'!$B$5:$B$14501<=$I$5)*('Stock Receipt and Despatch'!$J$5:$J$14501=$B9)*'Stock Receipt and Despatch'!$I$5:$I$14501)


With the Above formula i am getting the result from 01-04-2012 to 31-12-2012 only


so kindly suggest where is i am doing wrong


Thanks


SP

 

[Hui]

SP


I would have expected it to be like:

=SUMPRODUCT(('Stock Receipt and Despatch'!$B$5:$B$14501>=DATE(YEAR($I$5),4,1))*('Stock Receipt and Despatch'!$B$5:$B$14501<DATE(YEAR($I$5)+1,4,1))*('Stock Receipt and Despatch'!$B$5:$B$14501<=$I$5)*('Stock Receipt and Despatch'!$J$5:$J$14501=$B9)*'Stock Receipt and Despatch'!$I$5:$I$14501)


Note the extra condition I added

 

[sgmpatnaik]

Hello Sir,


Thanks for your quick replay


but i am getting the result is 0


Kindly assist


Thanks


SP

 

[Hui]

SP

Can you upload your file ?

 

[sgmpatnaik]

Sure Sir,


But i will mail my file to your mail ID with your permission


Thanks


SP

 

[Hui]

ok