Troubles with SUMPRODUCT and text to value

[tohegut]

Hi, thanks for all the help here!

I have a row that can contain cells with values of 'ABC1', 'ABC2', ... 'ABC9', and also blank.
(This row also contains integer values, I want to ignore these)

I want to sum up all the ABCX numbers, so if I have in Row 1:
ABC1 ABC1 ABC7
To sum up, this is easy with:
=SUMPRODUCT(--(LEFT(A1:C1,3)="ABC"),VALUE(RIGHT(A1:C1)))
and my result will be 9.

BUT! This formula does not work if there is a blank in the range, or some text.
I get a #VALUE error and I'm not sure how to fix it.
The problem must be because blanks and pure text can't be treated as a VALUE(), but I need to use this function to treat the number I extract from ABCX as a number.

Thanks for your time

 

[Aladin Akyurek]

Thanks for the help everyone.

this is the nicest one I think, I was limiting myself by using RIGHT() because it would not support numbers like ABC0.5
This method works fine. Thank you.

You are welcome.