Improvement. Shorter and accounts for empty cells:

=SUMPRODUCT(--(ISNUMBER(MATCH(INT(--SUBSTITUTE(A1:A6,"*",".")),{1;2;3;4},0))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
This works based on the following limitations:

No empty cells within the range
Every cell has a maximum of 2 numbers separated by an asterisk.

=SUMPRODUCT(--(INT(--SUBSTITUTE(A1:A6,"*","."))0),--(INT(--SUBSTITUTE(A1:A6,"*","."))<5))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The numbers are always positivie and whole numbers.

"T. Valko" wrote:

I need to count the cells where the first
number is greater than zero and less than five.

Does that mean some of the first numbers might be negative:

-5*10
0*00
-2*100
17*55

Are the numbers *always* whole numbers?

1.5*8
-0.2*77
5.7*7

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Mike: the formula works but is returning one additional cell because
I
was
not clear in my original message. I need to count the cells where the
first
number is greater than zero and less than five. How does this nested
formula
need to be modified. Thank you.

"Mike H" wrote:

Hi,

Try this array formula

=COUNT(IF((LEFT(A1:A20,FIND("*",A1:A20)-1)+0)<5,1))

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit
the
formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Basenji" wrote:

The column has data like this A1, 7*9; A2, 1*4; A3, 10*10. The
numbers
vary.
I need to count the number of cells that have a number less than
five
to the
left of the asterink. The asterink has nothing to do with
multiplication. I
have created a formula using the countif function,
=countif(A1:A200,1&"*")+countif(A1:A200,2&"*") etc for 3 and 4. It
works well
except when the number to the left of the asterink is 10, which
results
in
that cell being counted because of the wildcard to the right. How
can
the
formula be modified to just count based on the number 1 and not the
10
as the
characters to the right of the first number, 1, vary?