On Mon, 27 Aug 2012 09:17:23 +0000, Excel Dumbo wrote:


Hi Spencer,

Thanks a lot for your repliy. I tried your formula and it worked, it
gives me the result. But, it leads to another problem . I have explained
this below

For Eg Example- Result "Jul-12" is in Worksheet1 cell B1

On Worksheet2 , cell DG25 is linked to the B1 of Worksheet 1

I have a formula below

=SUMPRODUCT(($H$20:$BE$20=$DG$26)*($H$21:$BE$21<= $DG$25)*($H28:$BE28))

which pulls out the cumulative values from Jan to Jul
(H$21:$BE$21<=$DG$25) When I apply your formula mentioned in the earlier
post, my formula in Worksheet 2 gives me values only till June. The big
formula is supposed to give me values from Jan to July and not just Jan
to June.

Hope you understood the problem, as I am working on a big file, I am not
able to attach it here due to size restrictions.

Kindly advise.

Without knowing the contents of your row 26, my guess is that your formula is returning everything up to and including 7/1/2011. If you want to include the first seven months of the year (Jan-Jul inclusive), then you must change the formula in Sheet1!B1 to compute to the end of the month:

=EOMONTH(A2,12)

or

=DATE(YEAR(A2)+1,MONTH(A2)+1,0)