Sum delimited values in text string if...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J - 24 Feb 2007 00:13 GMT

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

Reply to this Message

Elkar - 24 Feb 2007 00:30 GMT

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

> 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
[quoted text clipped - 18 lines]
>
> Thanks

Reply to this Message

J - 24 Feb 2007 01:11 GMT

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?

> See if this works for you:
>
[quoted text clipped - 28 lines]
> >
> > Thanks

Reply to this Message

T. Valko - 24 Feb 2007 06:43 GMT

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

> Elkar,
>
[quoted text clipped - 43 lines]
>> >
>> > Thanks

Reply to this Message

J - 24 Feb 2007 08:49 GMT

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?

> Try this:
>
[quoted text clipped - 54 lines]
> >> >
> >> > Thanks

Reply to this Message

Ron Rosenfeld - 24 Feb 2007 13:10 GMT

>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
[quoted text clipped - 18 lines]
>
>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

Reply to this Message

J - 24 Feb 2007 17:02 GMT

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

> >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
[quoted text clipped - 31 lines]
> formula.
> --ron

Reply to this Message

Ron Rosenfeld - 24 Feb 2007 18:10 GMT

>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

Reply to this Message