formula sumproduct with criteria any letter at "end" of a number

[nd4spd]

I am using formula =SUMPRODUCT(ISNUMBER($L$11:$L$100)*($K$11:$K$100="Yes")) whereby i distinguish if column L is number or text.

Now due to a restructure (group 1) I have got number and a letter at the "end" (123A) and i want to be able to pick this up on the formula including the numbers.

Group 1 - numbers (1111) , numeric character (1123a)

Group 2 - alpha numeric (a123)

[benishiryo]

hi nd4spd, try:
=SUMPRODUCT(ISNUMBER(LEFT($L$11:$L$100)+0)*($K$11:$K$100="Yes"))

[nd4spd]

Thanks benishiryo . just to complete the scenario what do i change for Group 2 with letter at start =SUMPRODUCT((ISTEXT($L$11:$L$100)*($K$11:$K$100="Yes")))

[daddylonglegs]

Try

=SUMPRODUCT(ISERR((0&LEFT($L$11:$L$100))+0)*($K$11:$K$100="Yes"))

[nd4spd]

Thanks daddylonglegs it works !! Solved. but could one explain
1.=SUMPRODUCT(ISNUMBER(LEFT($L$11:$L$100)+0)*($K$11:$K$100="Yes"))
why zero at the end
why "LEFT" when it is RIGHT 1 character to check (123a) ISNUMBER(LEFT($L$11:$L$100)+0
and where this formula takes into account all numerical ?

2. 0& in this example ISERR((0&LEFT($L$11:$L$100))+0

thanks

[benishiryo]

the common factor between these 2 are that both starts with a number:
1111
1123a
so i used LEFT to find out if the 1st character is a number. but if you use LEFT, RIGHT or MID, it will turn numbers into a text. assuming A1 contains 1111, you can try
=LEFT(A1,1)=1
or:
=LEFT(A1)=1
it would be FALSE
but if you add a +0, it turns it to a number back again, provided it really is a number:
=LEFT(A1,1)+0=1

DDL's method to handle the other scenario is to use ISERR, which does the opposite of what happens with ISNUMBER scenario. when the 1st left character +0 is an alphabet, it will return a VALUE error (which will turn TRUE. and that's what you need. to count those that does not contain a number in the first character). he added a 0& in front to deal with blanks i suppose. if you take the 1st character of a blank, it will return nothing. nothing + 0 will give you a VALUE error. and you don't want to add that to your count.