Fine tune the counting area by setting up parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wilchong - 29 May 2008 03:41 GMT

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

Reply to this Message

T. Valko - 29 May 2008 06:20 GMT

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)

Signature

Biff
Microsoft Excel MVP

> 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

Reply to this Message

wilchong - 29 May 2008 07:20 GMT

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:
>
[quoted text clipped - 28 lines]
>> Many thanks,
>> Wilchong

Reply to this Message

T. Valko - 29 May 2008 17:39 GMT

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

Signature

Biff
Microsoft Excel MVP

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

Reply to this Message

wilchong - 30 May 2008 02:24 GMT

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?
[quoted text clipped - 4 lines]
>>>> Many thanks,
>>>> Wilchong

Reply to this Message

T. Valko - 30 May 2008 03:48 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Dear T. Valko,
> Yes, you are right, I just want to count the items in column B. However,
[quoted text clipped - 24 lines]
>>>>> Many thanks,
>>>>> Wilchong

Reply to this Message