Try sumproduct...
=SUMPRODUCT(--(A1:A13>=C1), --(A1:A13<=D1), --(B1:B13=E1))
here is a link for an explanation
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...
Jim Thomlinson
"Eric" wrote:
> Does anyone have any suggestions on how to determine the value?
>
> Under column A, there is a list of date sorted by descending order.
> Under column B, there is a list of numbers
> In cell C1, there is a starting date, and in cell D1, there is a ending
> date, and
> in cell E1, there is a number.
> I would like to determine the number of occurrence based on the value in
> cell E1 matching any value under column B between starting date [C1] and
> ending date [D1]. For example
>
> There are some data for column A & B
>
> 30-Aug-2007 8
> 29-Aug-2007 7
> 28-Aug-2007 6
> 27-Aug-2007 3
> 26-Aug-2007 5
> 25-Aug-2007 8
> 24-Aug-2007 1
> 23-Aug-2007 9
> 22-Aug-2007 8
> 21-Aug-2007 6
> 20-Aug-2007 3
> 19-Aug-2007 7
> 18-Aug-2007 5
> ...
>
> The value in cell E1 is 6
> The number of occurrence for 6 between 21-Aug-2007 and 28-Aug-2007 is 2,
> because there are 2 occurrence of number 6 between this period.
> 2 will return in cell F1.
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
>
Similar Threads
