only sum last three numbers

[thebeans]

hi all
I have a string of numbers 123456,but sometimes there could be a letter in there i.e 123x56,12345x.what I wont to do is sum up the last three numbers. I have this formula which works ok till a letter comes along
=IFERROR(SUMPRODUCT(--MID(AB8,ROW(INDIRECT("1:" & LEN(AB8))),1)),"").I have no idea how to get it to ignore the letter and just sum up the other two

thanks richard

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(AB8,ROW(INDIRECT(LEN(AB8)-2&":"&LEN(AB8))),1),0))

 

[Rick Rothstein]

Here is another formula (a non-volatile one) for you to consider...

=SUMPRODUCT(IFERROR(0+MID(RIGHT(AB8,4-ISNUMBER(-RIGHT(AB8,3))),{1,2,3,4},1),0))

 

[thebeans]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(AB8,ROW(INDIRECT(LEN(AB8)-2&":"&LEN(AB8))),1),0))

Thanks Tetra that works big thumbs up mate
thanks richard

 

[Peter_SSs]

If I have understood correctly, Tetra's odea could be made non-volatile and shorter as follows.
Still confirmed with CSE
{=SUM(IFERROR(--MID(RIGHT(AB8,3),{1,2,3},1),0))}

.. or if you don't like the CSE
=SUM(INDEX(IFERROR(--MID(RIGHT(AB8,3),{1,2,3},1),0),0))


Rick
Your formula does a different job, the requirement being to "ignore the letter and just sum up the other two"

 

[thebeans]

thanks rick and peter yes did go with =SUM(INDEX(IFERROR(--MID(RIGHT(AB8,3),{1,2,3},1),0),0)).I have a set of rows now with some at 0.what would I have to add to leave them blank
thanks Richard
[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Rick
Your formula does a different job, the requirement being to "ignore the letter and just sum up the other two"

Completely missed that.

Here is the formula I would have posted had I read the question correctly...

=SUMPRODUCT(IFERROR(0+MID(RIGHT(A1,3),{1,2,3},1),0))

 

[Peter_SSs]

If you are happy to have them just 'appear' blank then you could format that column with the custom format of 0;; or use Conditional Formatting but of course both of those things just hide the zeros, it doesn't remove them.

Otherwise, one way would be to use

=IF(SUM(INDEX(IFERROR(--MID(RIGHT(AB11,3),{1,2,3},1),0),0))=0,"",SUM(INDEX(IFERROR(--MID(RIGHT(AB11,3),{1,2,3},1),0),0)))

 

[Tetra201]

Another way would be

=IFERROR(1/(1/SUM(INDEX(IFERROR(--MID(RIGHT(AB11,3),{1,2,3},1),0),0))),"")