countif??

A

Antonio

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
 
M

Mike H

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
 
A

Antonio

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
 
D

David Biddulph

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.
 
M

Mike H

Maybe

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

Mike
 
M

Mike H

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
 
M

Mike H

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
 
A

Antonio

thats what i lookinf 4

David Biddulph said:
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.
Click to expand...
 
D

David Biddulph

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.
 
A

Antonio

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
 
R

RagDyer

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.
 
R

RagDyeR

Your feed-back is appreciated.
--

Regards,

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

Tks very much to all for the help given

With this formula, got the expected result

Tks
 
Top