Hi Bernie,

Thank you very much for your time and assistance.

The data is sorted by date and your inclusion ((MONTH(OFFSET(Data,0,1,,1))=
(MONTH(OFFSET(Data,1,1,,1)) has provided me with the required results. Great!

Cheers,
Sam

Bernie Deitrick wrote:
Sam,

If the data is sorted by date, then adding in this term:

((MONTH(OFFSET(Data,0,1,,1))=(MONTH(OFFSET(Data,1 ,1,,1))

will exclude the latest data point.

So the whole formula becomes:
=SUMPRODUCT((OFFSET(Data,0,2,,5)=F$25)*(MONTH(OFF SET(Data,0,1,,1))=MONTH($B28))*((MONTH(OFFSET(Data ,0,1,,1))=(MONTH(OFFSET(Data,1,1,,1)))*(YEAR(OFFSE T(Data,0,1,,1))=YEAR($C28))*(OFFSET(Data,0,1,,
1)<""))))

But since it is a count, why not just subtract 1?

HTH,
Bernie
MS Excel MVP

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1