third solution with an array formula:

=SUM(IF(MID(A1:A5;4;1)="a";VALUE((LEFT(A1:A5;2))); 0))
accept this formula with "ctrl + shift + enter", as it is an array formula!

"Ron Rosenfeld" wrote:

On 27 Sep 2006 04:26:27 -0700, wrote:

hello,

I got a row of data formated in xx-yy, where xx represents percentage
(number), - is delimiter from and yx definition as letter+number. (e.g.
55-A1, 60-V2...)

What I want to do is sum only first two xx values, if y equals A for
the whole row.

I tested the sum finction =SUM(VALUE(LEFT(a1:a10;2))), which in
function dialog view sums the values correctly, while in the
spreadsheet (after pressing enter), it returns a #VALUE! error.

What I came up with (and should work, as I understand) is
=SUMIF(LEFT(H8:K8;4);"*a*";LEFT(H8:K8;2))
=IF(H8:K8="*a*";SUM(LEFT(H8:K8;2));)

but it does not work. Where do I make a mistake?

Thanks!

Try this approach instead:

=SUMPRODUCT(LEFT(rng,FIND("-",rng)-1)*(MID(rng,FIND("-",rng)+1,1)="A"))

where your data is in "rng" (e.g. H8:K8)


--ron