On Tuesday, August 26, 2014 7:00:58 PM UTC+5:30, Claus Busch wrote:
Hi again,



Am Tue, 26 Aug 2014 14:53:05 +0200 schrieb Claus Busch:



if you have to sum EMV!AA2:AA3615 try:

=SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)



sorry, there is an error. Column C are numbers of month so you do not

need MONTH

Here is a shorter suggestion:

=SUMPRODUCT((((YEAR(EMV!A2:A3615)=YEAR(TODAY())-1)*(EMV!C2:C3615=4))+((YEAR(EMV!A2:A3615)=YEAR(TO DAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks Claus for this simple formula. I have only removed the "month" tag in the formula as C column is giving the serial no. of the month, which I presume is not a date field any more. After that it worked fine!

San

=SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())-1),--(EMV!C2:C3615=4),EMV!AA2:AA3615)+SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())),--(EMV!C2:C3615<=3),EMV!AA2:AA3615)