Fine tune the counting area by setting up parameters

[wilchong via OfficeKB.com]

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

 

[T. Valko]

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)

 

[wilchong via 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

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

 

[T. Valko]

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

--
Biff
Microsoft Excel MVP

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

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

 

[wilchong via 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

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]

 

[T. Valko]

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

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

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