On reflection this is batter

=SUM(IF(RIGHT(L49:CY49,3)="abc",LEFT(L49:CY49,LEN( L49:CY49)-3)*1))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Mike H" wrote:

Hi

With no blank cell in the range try

=SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3)))

Mike

"Simon Woods" wrote:

I have a worksheet where I need to calculate the sum of a range of cells
containing a text string and a number, for example:

If I had the cell values:
8ABC, 10ABC, 2XYZ
And I wanted to calculate the sum of the numeric portions of cells
containing the strong ABC, the result would be 18.

I know the text string will always be 3 characters long, and so I've tried
this:

=SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3)))

Which, I think, should check all cells in the range L49 to CY49 to see if
the rightmost 3 characters are"ABC" and, where that's true, assign the
numeric value of that cell by trimming the text string (so 10ABC becomes 10
and so on) and then adding those values together.

But, the formula dies on the first step, returning #value! for the function
RIGHT($L49:CY49). If I click the Insert Function button, the function appears
to evaluate correctly in the "Function arguments" dialogue box.

I've seen a similar function work in another spreadsheet, but the function
is surrounded by curly braces in the function bar. I don't know if that makes
a difference.

Any ideas what I'm doing wrong?