No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Brian" wrote:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

"Ron Coderre" wrote:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( "
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Brian" wrote:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?