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?

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?

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?

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?