You're wanting to count items in column B so what does column A have to do
with it?

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:84dce4c78ff08@uwe...
Dear T. Valko,
Many thanks for your advice. I trust your suggested formula is extremely
useful. Based on this formula, I have to check the database all the time
which one is the last one, and I think it is not efficient enough.

As a result, I change my requirement, instead of put the B col parameters
for
fine tuning the range, I suggest using the data from A col (1 to 5) to
expand
or narrow the counting area, e.g. I just put 1 to 10, or 1 to 98, and then
formula know to count the data on B col based on the parameters on D3.
Therefore, do you think you suggested formula:
=COUNTIF(INDEX(B:B,E3):INDEX(B:
B,F3),D3) can be revised to in order to meet my requirement?

I have tried to change "B" in the formula to "A", but I cann't manage to
count the data on B col, as a result I failed to get the result.

Many thanks,
Wilchong






T. Valko wrote:
Try this:

Enter the last cell you want to use for the range in cell E3.

E3 = 7

=COUNTIF(B3:INDEX(B:B,E3),D3)

The range will be B3:B7

If E3 = 100 the range will be B3:B100.

If E3 is empty it will calculate the entire range from B1 to B65536 (or
beyond, version dependent)

If you want to use 2 parameters, a start and an end:

E3 = start = 3
F3 = end = 7

=COUNTIF(INDEX(B:B,E3):INDEX(B:B,F3),D3)

The range will be B3:B7

If either E3 or F3 is empty it will calculate the entire range from B1 to
B65536 (or beyond, version dependent)

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: 1, 2, 3, 4 and 5
[quoted text clipped - 19 lines]
Many thanks,
Wilchong

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