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

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" 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

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?

"Elkar" wrote:

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" 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

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" wrote in message
...
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?

"Elkar" wrote:

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" 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

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?

"T. Valko" wrote:

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" wrote in message
...
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?

"Elkar" wrote:

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" 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

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

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

"Ron Rosenfeld" wrote:

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

On Sat, 24 Feb 2007 09:02:00 -0800, J wrote:

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