The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a formula
from my previous question but struck out. Thank you for your assistance.

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers
vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your assistance.

Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers
vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your assistance.

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1 :A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers
vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less
than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your
assistance.

Thank you. Unfortunately I am getting a value error. I have checked numerous
times for any typing errors but do not see any. What does the "~" refer to.
Also, I erred when I indicated that there will always be at least 2 numbers
in the cell. Upon rare occassions there may not be any numbers in the cell.
Hopefully that does not happen because it means sad news.

"T. Valko" wrote:

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1 :A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers
vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less
than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your
assistance.

To get this to work properly we will need to know what are *all* the
possible types of cell entires. Try to list a good representative sample of
*all* possible cell entries.

Are there any empty cells in the range? Are there any formulas in the range
that return formula blanks?

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Thank you. Unfortunately I am getting a value error. I have checked
numerous
times for any typing errors but do not see any. What does the "~" refer
to.
Also, I erred when I indicated that there will always be at least 2
numbers
in the cell. Upon rare occassions there may not be any numbers in the
cell.
Hopefully that does not happen because it means sad news.

"T. Valko" wrote:

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1 :A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The
numbers
vary
as well as the length. The numbers are always whole numbers and
never
negative. I need to count the number of cells that have a number
less
than
six immediately to the right of the first asterink from the left.
The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your
assistance.

Samples of data include: 8*9, 7*8*9, 0*5*6. There will always be at least 2
numbers separated by an *. The numbers for any of the positions can be as low
as 0 (zero) and a maximum of 10. They are always whole numbers, no decimals
or fractions. There are no negative numbers. There may be empty cells within
the range of cells. There are no formulas. So whether the cell data is 5*6 or
2*4*6, the objective is to count the number of cells in which the second
number is less than six. So with these two cells of data the return is one.

Thank you for your assistance.

"T. Valko" wrote:

To get this to work properly we will need to know what are *all* the
possible types of cell entires. Try to list a good representative sample of
*all* possible cell entries.

Are there any empty cells in the range? Are there any formulas in the range
that return formula blanks?

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Thank you. Unfortunately I am getting a value error. I have checked
numerous
times for any typing errors but do not see any. What does the "~" refer
to.
Also, I erred when I indicated that there will always be at least 2
numbers
in the cell. Upon rare occassions there may not be any numbers in the
cell.
Hopefully that does not happen because it means sad news.

"T. Valko" wrote:

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1 :A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The
numbers
vary
as well as the length. The numbers are always whole numbers and
never
negative. I need to count the number of cells that have a number
less
than
six immediately to the right of the first asterink from the left.
The
asterink has nothing to do with multiplication. I tried to modify a
formula
from my previous question but struck out. Thank you for your
assistance.

Ok, try this array formula** :

=SUM(IF(ISNUMBER(FIND("*",A1:A10)),--(--MID(A1:A10,FIND("*",A1:A10)+1,FIND("~",SUBSTITUTE( A1:A10&"*","*","~",2))-FIND("*",A1:A10)-1)<6)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Samples of data include: 8*9, 7*8*9, 0*5*6. There will always be at least
2
numbers separated by an *. The numbers for any of the positions can be as
low
as 0 (zero) and a maximum of 10. They are always whole numbers, no
decimals
or fractions. There are no negative numbers. There may be empty cells
within
the range of cells. There are no formulas. So whether the cell data is 5*6
or
2*4*6, the objective is to count the number of cells in which the second
number is less than six. So with these two cells of data the return is
one.

Thank you for your assistance.

"T. Valko" wrote:

To get this to work properly we will need to know what are *all* the
possible types of cell entires. Try to list a good representative sample
of
*all* possible cell entries.

Are there any empty cells in the range? Are there any formulas in the
range
that return formula blanks?

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Thank you. Unfortunately I am getting a value error. I have checked
numerous
times for any typing errors but do not see any. What does the "~" refer
to.
Also, I erred when I indicated that there will always be at least 2
numbers
in the cell. Upon rare occassions there may not be any numbers in the
cell.
Hopefully that does not happen because it means sad news.

"T. Valko" wrote:

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1 :A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The
numbers
vary
as well as the length. The numbers are always whole numbers and
never
negative. I need to count the number of cells that have a number
less
than
six immediately to the right of the first asterink from the left.
The
asterink has nothing to do with multiplication. I tried to modify
a
formula
from my previous question but struck out. Thank you for your
assistance.