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

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)


--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:84dafc1935f4f@uwe...
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

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

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

Dear T. Valko,
Yes, you are right, I just want to count the items in column B. However, I
think if I can use the data in column A to fine tune the counting range in
column B, it will increase my productivity. For example, let say I want to
count how many "2" in column B from the range 1 (A3) to 5 (A7). My ideal
solution is that just setting two parameters: 1 in E3 and 5 in F3, the Excel
formula will know to search and count the items in column B based on another
parameters in D3.

I tired to revise the formula =COUNTIF(INDEX(B:B,E3):INDEX(B:B,F3),D3) in
order to meet the requirement, but failed.

Many thanks for your time and effort,
Wilchong




T. Valko wrote:
You're wanting to count items in column B so what does column A have to do
with it?

Dear T. Valko,
Many thanks for your advice. I trust your suggested formula is extremely
[quoted text clipped - 49 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

Ok, just change the referenced ranges to start at B3:

=COUNTIF(INDEX(B3:B100,E3):INDEX(B3:B100,F3),D3)

E3 = 1
F3 = 5

The range would be B3:B7

Adjust the end of the range as needed.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:84e6e2d56525e@uwe...
Dear T. Valko,
Yes, you are right, I just want to count the items in column B. However,
I
think if I can use the data in column A to fine tune the counting range in
column B, it will increase my productivity. For example, let say I want
to
count how many "2" in column B from the range 1 (A3) to 5 (A7). My ideal
solution is that just setting two parameters: 1 in E3 and 5 in F3, the
Excel
formula will know to search and count the items in column B based on
another
parameters in D3.

I tired to revise the formula =COUNTIF(INDEX(B:B,E3):INDEX(B:B,F3),D3) in
order to meet the requirement, but failed.

Many thanks for your time and effort,
Wilchong




T. Valko wrote:
You're wanting to count items in column B so what does column A have to do
with it?

Dear T. Valko,
Many thanks for your advice. I trust your suggested formula is
extremely
[quoted text clipped - 49 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1