count problem

[bill gras]

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

 

[Aladin Akyurek]

Replace "result" with 0 in AV38 and enter in AV39 & copy down:

=IF(OR(ISERROR(AU39),SUMIF(AU39,">35")>35),
"",
IF(IF(ISERROR(AU40),1,AU40>35),

COUNTIF(INDEX($AU$39:AU39,MATCH(9.99999999999999E+307,$AV$38:AV38)):AU39,"<=35"),
""))

 

[bill gras]

Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU

row 39 32
40 14
41 19 3
42 107
43 11
44 21 2
45 #value!
46 40
47 45
48 16
49 15 2
50 #value!
51 29
52 11 2
53 146
54 14
55 17
56 11 3
57 #value!

I hope you can still help me as I'm nearly finished my project
Thanking You
regards bill
bill gras

 

[Aladin Akyurek]

Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU

[...]

Are you trying to say that 3 should appear at the same row as 107 and 2
at the same row as 40?

 

[bill gras]

Hi Aladin
No . What I'm saying is that number 3 should be the only number (where it is)
and no number 2 because 107 is greater than 35 and that should be the cut off
point , there fore the next sequence should start after the #value! in
column AU45 . So the next sequence starts at AU 46 where the first number is
greater than 35 and that is the cut off point (no result) . The next sequence
starts at AU 51 after the #value! in AU50 and number 2 is correct , the cut
off point is at AU53 which the number is 146 so no number 3 . The next
sequence starts at AU58 after the #value! of AU57.

I hope this will help , please don't give up
thanks bill
--
bill gras

Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU

[...]

Are you trying to say that 3 should appear at the same row as 107 and 2
at the same row as 40?

 

[Domenic]

Let AU38 contain #VALUE!...

AV39, copied down:

=IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$500>35,0),0)-1,"")

Hope this helps!

 

[Domenic]

Please ignore...

Let AU38 contain #VALUE!...

AV39, copied down:

=IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$500>35,0),0)-1,"")

Hope this helps!

 

[Aladin Akyurek]

Hi Aladin
No . What I'm saying is that number 3 should be the only number (where it is)
and no number 2 because 107 is greater than 35 and that should be the cut off
point , there fore the next sequence should start after the #value! in
column AU45 . So the next sequence starts at AU 46 where the first number is
greater than 35 and that is the cut off point (no result) . The next sequence
starts at AU 51 after the #value! in AU50 and number 2 is correct , the cut
off point is at AU53 which the number is 146 so no number 3 . The next
sequence starts at AU58 after the #value! of AU57.

I hope this will help , please don't give up
thanks bill

A38:AV57 looks like this, produced with:

=IF(OR(ISERROR(AU39),SUMIF(AU39,">35")>35),
"",
IF(IF(ISERROR(AU40),1,AU40>35),
COUNTIF(INDEX($AU$39:AU39,
MATCH(9.99999999999999E+307,$AV$38:AV38)):AU39,"<=35"),
""))

0
32
14
19 3
107
11
21 2
#VALUE!
40
45
16
15 2
#VALUE!
29
11 2
146
14
17
11 3
#VALUE!

What the formula does is to count all values < 35 between the last count
and just before a value > 35 or before a #VALUE!. If you want to,
reproduce the desired counts using the quoted section.

 

[bill gras]

Hi Domenic
Thanks for your reply and your time , your fomula works perfect
Once again thanks
regards bill

 

[Domenic]

Bill,

The formula will return an incorrect result when a sequence does not
contain a number greater than 35. Therefore, try the following formula
instead...

=IF(ISERR(AU38),COUNTIF(AU39:INDEX(AU39:$AU$500,MATCH(TRUE,IF(ISNUMBER(AU
39:$AU$500),AU39:$AU$500>35,ISERR(AU39:$AU$500)),0)),"<=35"),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[bill gras]

Hi Domenic

I was just about to post to say that I came up with an incorrect result ,
but you beat me to it .
Your new formula works perfect all the way down to 500 rows , no errors !
You are a gentleman and genius.
Thank you very much !

regards bill