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