count based on single number

[Basenji]

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 H]

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]

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.

 

[Teethless mama]

=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

 

[Mike H]

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

 

[T. Valko]

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

 

[Basenji]

The numbers are always positivie and whole numbers.

 

[Basenji]

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.

 

[T. Valko]

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))

 

[T. Valko]

Improvement. Shorter and accounts for empty cells:

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

 

[Basenji]

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?

 

[David Biddulph]

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

 

[T. Valko]

=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

 

[Basenji]

Many thanks for the explanation.

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


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