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?

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?

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?

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

ctrl+shift+enter, not just enter


"Basenji" wrote:

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?

Hi,

Not and improvement on TM's formula, just different

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

Ctrl+Shift+Enter

Mike

"Basenji" wrote:

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?

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?

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?

Now I need a variation of this formula. The cell content is the same, 5*4,
etc. This time I need to count the the number of cells in which the second
number of this string, ie 4, is less than 6. I have tried to adapt the
original formula but have been unsuccessful. Thank you for your assistance.

"Mike H" wrote:

Hi,

Not and improvement on TM's formula, just different

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

Ctrl+Shift+Enter

Mike

"Basenji" wrote:

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?

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?

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?

Thank you. It works. Hopefully I can figure out how and why it works. What do
the asterisk and the period between the quotes refer to and what does the 0
at the end of the formula refer to?

"T. Valko" wrote:

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?

SUMPRODUCT, ISNUMBER, MATCH, INT, and SUBSTITUTE are Excel functions, and
their syntax can be found in Excel help.
--
David Biddulph

"Basenji" wrote in message
...
Thank you. It works. Hopefully I can figure out how and why it works. What
do
the asterisk and the period between the quotes refer to and what does the
0
at the end of the formula refer to?

"T. Valko" wrote:

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?

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

What do the asterisk and the period between
the quotes refer to and what does the 0 at the
end of the formula refer to?

Let's use this data to see what the formula is doing:

10*22
3*17

Assuming the cell will only contain 2 numbers and the astersik...

The formula is replacing the astersik with a dot or decimal point:

SUBSTITUTE(A1:A6,"*",".")

10.22
3.17

This in effect turns the entries into decimal numbers. However, the
SUBSTITUTE function returms a TEXT value (even if it looks like a number) so
we use the double unary "--" to coerce that TEXT number into a numeric
number:

--SUBSTITUTE(A1:A6,"*",".")

Now that we have numeric values and since we're only interested in testing
the first number we use the INT function to reduce the numbers to their
integers:

INT(--SUBSTITUTE(A1:A6,"*","."))

10.22 = 10
3.17 = 3

The criteria of the formula is to count how many first numbers are 0 and <5
so we now use the MATCH function to look at these first numbers and see if
they match any of the numbers {1;2;3;4}.

MATCH({10;3},{1;2;3;4},0)

The 0 means we want to find exact matches only.

10 does not match any of the numbers 1,2,3,4 but 3 does match one of these
numbers 1,2,3,4

When a match occurs, the MATCH function will return the numeric position of
the match. When there is not a match the MATCH function will return the
error #N/A.

Since 10 does not match we get #N/A. 3 matches the 3 and the 3 is in the 3rd
position so we get 3. So now we have {#N/A;3}.

We use the ISNUMBER function to test these values and see if any of them are
numbers

ISNUMBER({#N/A;3})

ISNUMBER will return TRUE when the value is a number and FALSE when the
value is not a number so we get:

ISNUMBER({FALSE;TRUE})

SUMPRODUCT works with numbers so we need to convert the TRUE and FALSE to
numbers. We use the double unary once again:

--(ISNUMBER({FALSE;TRUE})

--TRUE = 1
--FALSE = 0

And finally SUMPRODUCT sums up the 1s and 0s for a final result:

=SUMPRODUCT({0;1}) = 1

So, how many of these first numbers are 0 and <5:
10*22
3*17

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

=1

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Thank you. It works. Hopefully I can figure out how and why it works. What
do
the asterisk and the period between the quotes refer to and what does the
0
at the end of the formula refer to?

"T. Valko" wrote:

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?

Many thanks for the explanation.

"T. Valko" wrote:

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

What do the asterisk and the period between
the quotes refer to and what does the 0 at the
end of the formula refer to?

Let's use this data to see what the formula is doing:

10*22
3*17

Assuming the cell will only contain 2 numbers and the astersik...

The formula is replacing the astersik with a dot or decimal point:

SUBSTITUTE(A1:A6,"*",".")

10.22
3.17

This in effect turns the entries into decimal numbers. However, the
SUBSTITUTE function returms a TEXT value (even if it looks like a number) so
we use the double unary "--" to coerce that TEXT number into a numeric
number:

--SUBSTITUTE(A1:A6,"*",".")

Now that we have numeric values and since we're only interested in testing
the first number we use the INT function to reduce the numbers to their
integers:

INT(--SUBSTITUTE(A1:A6,"*","."))

10.22 = 10
3.17 = 3

The criteria of the formula is to count how many first numbers are 0 and <5
so we now use the MATCH function to look at these first numbers and see if
they match any of the numbers {1;2;3;4}.

MATCH({10;3},{1;2;3;4},0)

The 0 means we want to find exact matches only.

10 does not match any of the numbers 1,2,3,4 but 3 does match one of these
numbers 1,2,3,4

When a match occurs, the MATCH function will return the numeric position of
the match. When there is not a match the MATCH function will return the
error #N/A.

Since 10 does not match we get #N/A. 3 matches the 3 and the 3 is in the 3rd
position so we get 3. So now we have {#N/A;3}.

We use the ISNUMBER function to test these values and see if any of them are
numbers

ISNUMBER({#N/A;3})

ISNUMBER will return TRUE when the value is a number and FALSE when the
value is not a number so we get:

ISNUMBER({FALSE;TRUE})

SUMPRODUCT works with numbers so we need to convert the TRUE and FALSE to
numbers. We use the double unary once again:

--(ISNUMBER({FALSE;TRUE})

--TRUE = 1
--FALSE = 0

And finally SUMPRODUCT sums up the 1s and 0s for a final result:

=SUMPRODUCT({0;1}) = 1

So, how many of these first numbers are 0 and <5:
10*22
3*17

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

=1

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Thank you. It works. Hopefully I can figure out how and why it works. What
do
the asterisk and the period between the quotes refer to and what does the
0
at the end of the formula refer to?

"T. Valko" wrote:

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?