Count the number of cell based on the value of certain cell

[wilchong via OfficeKB.com]

Dear sir,

There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 22, 21, 20, 19, 18, 17 and 16
In the cell of B7 is 1 and B1 to B6 is empty (no value).

My question is that I need a formula which can count from 1 in the cell of B7
and result show in B1 is 7; the result in B1 will be 10 if 5 is in the cell
of B7. I tried to use COUNT() function, but the result won't change if I
change the value in the cell of B7. Do you think an Excel function can
achieve the result according my requirement?

Many thanks, Wilchong

 

[Max]

I'm guessing that there's a direct association with the numbers in A1:A7
In B1, copied to B6: =RANK(A1,$A$1:$A$6,1)+$B$7
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100, Files:360, Subscribers:56
xdemechanik

 

[Ron@Buy]

Wilchong
I cannot follow your logic - what are you trying to achieve?
What is the relevance of the entries in cells A1 to A7 in relation to the
entries in column B??
You say that a result of 7 is in B1 - the only COUNT I can see that will
achieve that is the number of cells in column A that contain figures. Why
therefore does the number in B1 only increase by 3 if you enter a 5 in cell
B7?
Please provide more details of the relationship between your data and what
do you want to count?

 

[wilchong via OfficeKB.com]

I'm guessing that there's a direct association with the numbers in A1:A7
In B1, copied to B6: =RANK(A1,$A$1:$A$6,1)+$B$7

There is 1 data set is assumed running in A1 down, viz.:
In A1 down is: 22, 21, 20, 19, 18, 17 and 16

[quoted text clipped - 8 lines]

change the value in the cell of B7. Do you think an Excel function can
achieve the result according my requirement?

Dear Max,
I have tired to apply your suggested formula and it proof very effective.
However, in considering a new situation that the data in column "A" are
always increasing. As a result, in order to avoid always revise the range
in the formular, I set one parameter in the cell of C1 and put a value, 16 in
the cell. And then I apply the following formular in the cell of B1:
=INDEX($A$1:$B$7,MATCH(C1,$A$1:$A$7,0),2).

However, this formula is only achieved partial objective. The Index(..(match)
) formular is only can detect a correspondence data with the help from cell
of C1, but it cannot compute the number of cell from A6 to A1 in order to
arrive 7. Do you think it is possible to add other formular after my
formula in order to achieve the result.

Many thanks with your advice, Wilchong

 

[Max]

.. compute the number of cell from A6 to A1 in order to arrive 7

Generally, you could use
=COUNTA(A:A)
to count the above

Or, simply adjust the expression
with an arithmetic addition/subtraction like this eg:
=COUNTA(A:A)+1
=COUNTA(A:A)-1
if you want a number one greater/less than
the number of filled cells in the range for whatever purpose

The above expressions could be used within say, an OFFSET
as the height param to function as a dynamic range
(this assumes cells filled continuously from row1 down in col A)

I don't know what you're trying to do. Perhaps better for you to post afresh
as a new thread, and explain clearly your underlying intents from scratch.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---

 

[wilchong via OfficeKB.com]

Generally, you could use
=COUNTA(A:A)
to count the above

Or, simply adjust the expression
with an arithmetic addition/subtraction like this eg:
=COUNTA(A:A)+1
=COUNTA(A:A)-1
if you want a number one greater/less than
the number of filled cells in the range for whatever purpose

The above expressions could be used within say, an OFFSET
as the height param to function as a dynamic range
(this assumes cells filled continuously from row1 down in col A)

I don't know what you're trying to do. Perhaps better for you to post afresh
as a new thread, and explain clearly your underlying intents from scratch.

Good morning Max,
Many thanks for your time, I have solved the problem already.

Thanks a lot!
Wilchong

 

[Max]

Ok, guess you know best what's happening over there
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100, Files:360, Subscribers:56
xdemechanik