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?