#Value! error when evaluating data in a cell range

[Simon Woods]

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?

 

[Glenn]

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?

Lookup "About array formulas and array constants" in the help file.

 

[krcowen]

Simon

The curly brackets make all the difference. If you enter the formula
with shitt-control-enter (all three at once) Bill Gates will add the
curly brackets, and your formula will probably work

Good luck

Ken

 

[Mike H]

Hi

With no blank cell in the range try

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

Mike

 

[Mike H]

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

 

[Simon Woods]

Cheers Glenn, that sorted out the #value! problem. Still isn't working yet
though, just returns 0 instead.

 

[Simon Woods]

That works perfectly, thank you.

One question, though. What does the *1 do at the end of the LEN() statement
that means it works, but the same statement without it does not? Not
critical, I'm just curious

 

[Mike H]

Hi,

the string being evaluated is text so the formula returns text so
multiplying the text number ("22") *1 turns it into a real number (22).

Mike

 

[Mike H]

Apologies,

i forgot to mention thanks for the feed back and how refreshing it is to
have a poster enquire as to how their problem is solved.

Mike