Help require to find out what is wrong in my formula

[mso3]

Hello friends,
There are two particulars in item Grocery’ coffee and rice in the month of April in between dates 1/4/14 to 31/5/14 and the total amount is 35+45 = 80.
I have entered a start date 1/4/14 in cell AN3 and end date 31/5/14 in cell AO3. I have selected a item ‘Grocery’ in cell AP3.

Now at this point (after entering start date, end date and item) the total amount should be displayed in the table in the month column before the particulars respectively.

I have colored the item Grocery, particulars, date and amount red for your reference.

Please see the formula in the table and suggest me what is wrong in it.

Thanking you in anticipation.

[DannyJ]

try this

=SUMPRODUCT((L2:L25<=AO3)*(L2:L25>=AN3)*(P2:P25))

[mso3]

Hi,
No, it's displaying a total 80 before a first particular Beans which is wrong.

The output should be as follows:

In colun AD month April
Coffee: 35
Rice: 45

and all other particulars should have a 0 amount before them in column AD in the month of April. No need of grand total 80. I want to sum a each item particulars in the respective column of month and before the particulars.

Hope this will help you to suggest me a solution.

[bebo021999]

Your date criterias:

...($L$2:$L$25=$AN$3)*($L$2:$L$25=$AO$3)...

should be:

...($L$2:$L$25>=$AN$3)*($L$2:$L$25<=$AO$3)...

[mso3]

Hi,
Thank you to both of you. The correct formula is:

=SUMPRODUCT(($E$2:$E$25=$AC5)*(TEXT($L$2:$L$25,"mmmm")=AD$4)*($L$2:$L$25>=$AN$3)*($L$2:$L$25<=$AO$3)*($D$2:$D$25=$AP$3)*$P$2:$P$25)

Thank you and have a nice time.