Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Mike tks for your prompt reply.

need only to count the last (in this case 3) < from 4

is it possible to adapt the formula??

Tks in advance

"Mike H" wrote:

Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

My reading of the question is that the requirement is to start counting with
the last non-blank cell in the column, and move up the column counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Maybe

=COUNTIF((INDIRECT("A" &MATCH(10^100,A:A)-2 &":a" & MATCH(10^100,A:A))),"<4")

Mike

"Antonio" wrote:

Mike tks for your prompt reply.

need only to count the last (in this case 3) < from 4

is it possible to adapt the formula??

Tks in advance

"Mike H" wrote:

Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Maybe better

Use this and enter into B1 to amount of cells from the bottom you want to
include in the calculation.

=COUNTIF((INDIRECT("A" &MATCH(10^100,A:A)-(B1-1) &":a" &
MATCH(10^100,A:A))),"<4")

Mike



"Mike H" wrote:

Maybe

=COUNTIF((INDIRECT("A" &MATCH(10^100,A:A)-2 &":a" & MATCH(10^100,A:A))),"<4")

Mike

"Antonio" wrote:

Mike tks for your prompt reply.

need only to count the last (in this case 3) < from 4

is it possible to adapt the formula??

Tks in advance

"Mike H" wrote:

Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

I amost certainly don't understand but my latest guess is take the last n
values in the column and see how may are <4

We shall see:)

Mike

"David Biddulph" wrote:

My reading of the question is that the requirement is to start counting with
the last non-blank cell in the column, and move up the column counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

This formula also returns 14:

=COUNTIF(A:A,"<4")

Pete

On May 9, 11:07*am, Antonio wrote:
Hi all

Looking for a formula that count's, from last cell (undifined) upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start counting with
the last non-blank cell in the column, and move up the column counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

The number of cells over which to count (the number you want to put in B1)
is the number (counting upwards from he foot of the column) until you get to
a cell with 4 in it.
--
David Biddulph

"Mike H" wrote in message
...
Maybe better

Use this and enter into B1 to amount of cells from the bottom you want to
include in the calculation.

=COUNTIF((INDIRECT("A" &MATCH(10^100,A:A)-(B1-1) &":a" &
MATCH(10^100,A:A))),"<4")

Mike



"Mike H" wrote:

Maybe

=COUNTIF((INDIRECT("A" &MATCH(10^100,A:A)-2 &":a" &
MATCH(10^100,A:A))),"<4")

Mike

"Antonio" wrote:

Mike tks for your prompt reply.

need only to count the last (in this case 3) < from 4

is it possible to adapt the formula??

Tks in advance

"Mike H" wrote:

Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined)
upwards how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Then that's what you have in my previous post

"Antonio" wrote:

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start counting with
the last non-blank cell in the column, and move up the column counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Hi mike

tryed it, and seems it's not working, because when column
a "grows and more then a 4 in it, returns the total of non 4

"Mike H" wrote:

Then that's what you have in my previous post

"Antonio" wrote:

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start counting with
the last non-blank cell in the column, and move up the column counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" & MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined) upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

With data in Column A, this *array* formula will give you the number of
cells between the last 4 and the last cell containing a number:

=MATCH(99^99,A1:A50)-MAX(IF(A1:A50=4,ROW(1:50)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

This will count cells between the last 4 and the last number, whether or not
there is any data in those "in-between" cells.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Antonio" wrote in message
...
Hi mike

tryed it, and seems it's not working, because when column
a "grows and more then a 4 in it, returns the total of non 4

"Mike H" wrote:

Then that's what you have in my previous post

"Antonio" wrote:

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start
counting with
the last non-blank cell in the column, and move up the column
counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" &
MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined)
upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Tks very much to all for the help given

With this formula, got the expected result

Tks

"RagDyer" wrote:

With data in Column A, this *array* formula will give you the number of
cells between the last 4 and the last cell containing a number:

=MATCH(99^99,A1:A50)-MAX(IF(A1:A50=4,ROW(1:50)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

This will count cells between the last 4 and the last number, whether or not
there is any data in those "in-between" cells.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Antonio" wrote in message
...
Hi mike

tryed it, and seems it's not working, because when column
a "grows and more then a 4 in it, returns the total of non 4

"Mike H" wrote:

Then that's what you have in my previous post

"Antonio" wrote:

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start
counting with
the last non-blank cell in the column, and move up the column
counting up
while the cell value is not 4, and stopping when a 4 is encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with this
formula

=COUNTIF((INDIRECT("a1:a" &
MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined)
upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António

Your feed-back is appreciated.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Antonio" wrote in message
...
Tks very much to all for the help given

With this formula, got the expected result

Tks

"RagDyer" wrote:

With data in Column A, this *array* formula will give you the number of
cells between the last 4 and the last cell containing a number:

=MATCH(99^99,A1:A50)-MAX(IF(A1:A50=4,ROW(1:50)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

This will count cells between the last 4 and the last number, whether or
not
there is any data in those "in-between" cells.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Antonio" wrote in message
...
Hi mike

tryed it, and seems it's not working, because when column
a "grows and more then a 4 in it, returns the total of non 4

"Mike H" wrote:

Then that's what you have in my previous post

"Antonio" wrote:

thats what i lookinf 4

"David Biddulph" wrote:

My reading of the question is that the requirement is to start
counting with
the last non-blank cell in the column, and move up the column
counting up
while the cell value is not 4, and stopping when a 4 is
encountered.
--
David Biddulph

"Mike H" wrote in message
...
Hi,

Why are there only 3 numbers <4 in that list? I count 14 with
this
formula

=COUNTIF((INDIRECT("a1:a" &
MATCH(9.99999999999999E+307,A:A))),"<4")

Mike

"Antonio" wrote:

Hi all

Looking for a formula that count's, from last cell (undifined)
upwards
how
many cell < from 4, which from the list below should return 3

3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

tks in advance
António