On Fri, 23 Feb 2007 16:13:00 -0800, J wrote:

I would like to be able to sum numbers from a portion of a text string if the
number meets a condition. The cells of data are in a row. The data looks like
the following:
1x1.5
21x9
3x2

The number of digits is not a fixed length. The "x" consistently delimits
the numbers.

This formula is to give me the value of the right number if the left number
= 1. It seems to work fine.
=SUMPRODUCT((LEFT(B10,FIND("x",B10)-1)="1")*(RIGHT(B10,LEN(B10)-FIND("x",B10))))

I need to be able to sum all the right numbers when the value of the left
number = 1 over a range of cells, B10:BD10. When I use the formula below I
get a #Value! error:
=SUMPRODUCT((LEFT(B10:BD10,FIND("x",B10:BD10)-1)="1")*(RIGHT(B10:BD10,LEN(B10:BD10)-FIND("x",B10:BD10))))

Can anyone tell me what I can do to make the formula work?

Thanks


Assuming the number you will be looking for on the left can be any number, put
that number in some cell and name the cell LeftNum.

Then use this **array** formula:

=SUM(IF(LEFT(rng,LEN(LeftNum)+1)=LeftNum&"x",
--MID(rng,LEN(LeftNum)+2,255),0))

To enter an **array** formula, after placing it in the formula bar, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.
--ron