Sum delimited values in text string if...

[J]

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

 

[Elkar]

See if this works for you:

=SUM(IF(LEFT(B10:BD10,2)="1x",--MID(B10:BD10,3,99),0))

This is an array formula, so should be entered with CTRL-SHIFT-ENTER rather
than just Enter. If done properly, the formula should be surround by { }.

HTH,
Elkar

 

[J]

Elkar,

Thanks! This works fine with one exception. If the number to the left of the
"x" more than one digit long it does not appear to sum the number to the
right of the "x". Any suggestions to fix this?

 

[T. Valko]

Try this:

Array entered:

=SUM(IF(ISNUMBER(FIND("1x",B10:BD10)),--MID(B10:BD10,FIND("x",B10:BD10)+1,10)))

FIND is case sensitive so the formula is looking for lower case "x". If you
might have both "x" or "X" then replace FIND with SEARCH.

Biff

 

[J]

I am coming across something very strange. The formula is working perfectly
if the number on the left side of the "x" is 9 or higher. When the number on
the left side of the "x" is 1 through 8, the number on the right side of the
"x" is incorrect. For example if the number to the right of the "x" =1 it
will total 2. If it is 0.1 it will total 0.2. If it is 0.3 it will total 0.4.
Any ideas as to why?

 

[Ron Rosenfeld]

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

 

[J]

Thanks Ron. It is working great. I really appreciate the help.

 

[Ron Rosenfeld]

Thanks Ron. It is working great. I really appreciate the help.

Glad you finally got it working. Thanks for the feedback.

One of the issues, of course, was that it took a while before your requirements
were clarified.
--ron