The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: 1, 2, 3, 4 and 5
In B3 down is: 2, 3, 2, 6 and 8.
In D3 is a parameter which show a value of 8.
In E 3 is a Excel formula: =COUNTIF(B3:B7,D3) and the returned result is 1.

However, if I want to expand or narrow the counting area, e.g. from B3 to
B100 (if A3 down is: 1, 2, 3 ...... 98), I have to change the parameter in
the formula, from B7 to B100. My question is that is there any way to do
this step even more efficient because I will repeat this step many many times.
SUMPRODUCT cannot fully satisfied my requirement. The ideal solution for me:
set another two parameters, and then the Excel formula will based the two
parameters expand or narrow the searching area by just changing the two
parameters and count based on the parameters on D3. Do you think the
existing Excel formula: =COUNTIF(B3:B7,D3) can be transformed or modified to
the formula which can meet my requirement?

Many thanks,
Wilchong

--
Message posted via http://www.officekb.com