LEFT and SUMPRODUCT functions combined

I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.


Example:

14T	16
40
20	150
97L	67
13	12

For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).

I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns. Thanks for any help.

Somewhat. Any number in Column A that also has text will always be 2 digits followed by a single letter. Any number that does not have text might be any number, never more than 3 digits (0-999).

Originally Posted by Tony Valko

This array formula** entered in C2:

=SUM(IF(ISTEXT(A2:A10),LEFT(A2:A10,2),A2:A10)*B2:B10)

** 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.

Yup. This formula worked. Thanks a lot!

Now, is there a way to write the formula so that the user doesn't have to use CTRL + SHIFT + ENTER when the formula is entered?

Ok, thanks.