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

"bill gras" wrote in message
...
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