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

 

[admiral100]

Hi,

TRy this : Ctrl +Shift+Enter not just Enter

=SUMPRODUCT(--(LEFT(A1:D1,3)="ABC"),VALUE(IFERROR(RIGHT(A1:D1)*1,0)))

 

[tohegut]

Hi,

TRy this : Ctrl +Shift+Enter not just Enter

=SUMPRODUCT(--(LEFT(A1:D1,3)="ABC"),VALUE(IFERROR(RIGHT(A1:D1)*1,0)))

Wow, that's it. Thank you!

 

[tohegut]

=SUMPRODUCT(--(LEFT(A1:D1,3)="ABC"),VALUE(IFERROR(RIGHT(A1:D1)*1,0)))

This takes the rightmost character of a cell and multiplies by 1, if there's an error it uses 0.
Why do we multiply by 1?
And why is a array formula necessary?

I think we multiply by 1 so that, if it's not a number, we get an error before we try to convert it using VALUE().
I have used array formula before but sometimes I not sure when best to use them.

Thanks again for helping me with this.

 

[ITWare2008]

=SUMPRODUCT(--(LEFT(A1:D1,3)="ABC"),VALUE(IFERROR(RIGHT(A1:D1)*1,0)))

This takes the rightmost character of a cell and multiplies by 1, if there's an error it uses 0.
Why do we multiply by 1?
And why is a array formula necessary?

I think we multiply by 1 so that, if it's not a number, we get an error before we try to convert it using VALUE().
I have used array formula before but sometimes I not sure when best to use them.

Thanks again for helping me with this.

In this case we use an array formula because left and right functions are delivering arrays results whereas they are intend to deliver a single result only.

Take a look at Mike 'ExcelIsFun' Girvin youtube channel and/or read his book CTRL+SHIFT+ENTER for further explanations.

Vandalo

 

[admiral100]

Wow, that's it. Thank you!

You are welcome . thank you for the feedback

 

[Aladin Akyurek]

Either just enter:

=SUMPRODUCT(SUBSTITUTE(UPPER(0&A1:D1),"ABC",0)+0)

or control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("ABC",A1:D1)),SUBSTITUTE(UPPER(A1:D1),"ABC","")+0))

 

[shuklaankur281190]

@aladian

I think array formula would work positively if any cell doesn't contain ABC then non array formula wouldn't work ?

 

[Aladin Akyurek]

@aladian

I think array formula would work positively if any cell doesn't contain ABC then non array formula wouldn't work ?

That's why the second array-processing formula would be a better choice.

 

[tohegut]

Thanks for the help everyone.

or control+shift+enter, not just enter:
=SUM(IF(ISNUMBER(SEARCH("ABC",A1:D1)),SUBSTITUTE(UPPER(A1:D1),"ABC","")+0))

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.