count number of cells

[Guest]

I need a formula to count the number of cells in a column that have numbers
in them and are less than 46 (or cell B2 which equals to 46), but stops at a
blank cell , and then counts again how many cells have numbers in them less
than 46 (or B2) but stops at the next blank cell and so on eg:
A B
1 71
2 55
3 36
4 72
5 15
6 80
7 95 result 2
8 blank cell
9 17
10 22
11 90
12 35 result 3
13 blank cell
14 15
15 20
16 23
17 44
18 100 result 4
19 blank cell
20 21
21 17 result 2
22 blank cell
down to 500 rows
Any help will appreciated

thanks bill gras



bill gras

 

[Biff]

Hi!

I don't understand your explanation.

Your sample data seems to span A1:A21 (excluding the blank cell in A22) and
column B is blank except for the desired results yet you're are making some
sort of reference to cell B2 (which you say equals 46). Is this reference to
cell B2 (that equals 46) on a different sheet?

Here's one way based on the posted sample data:

In B1 enter 0

In B2 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(A2="",0,IF(AND(A2<>"",A3=""),COUNTIF(INDIRECT("A"&MAX((B$1:B1<>"")*(ROW(B$1:B1)))):A2,"<46"),""))

Copy down as needed.

Where blank cells appear in column A the corresponding cell in column B will
have a zero. If you don't want to see these then you can use a custom format
to hide them:

0;-0;;@

Biff

 

[Biff]

Hi!

Something I thought about after I had logged off for the evening.....

I guess it's possible for the first entry, A1, to be <46 and A2 could be
blank. So, in B1 instead of entering zero enter this formula:

=(A1<46)*1

Biff

 

[Guest]

Hi Biff
In cell B2 is a formula that equals to 46 which I forgot to put in.
It works perfectly! and once again Thank You

regards bill gras

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hi Biff
In cell B2 is a formula that equals to 46 which I forgot to put in.
It works perfectly! and once again Thank You

regards bill gras