Needing help to add up a column

[jimboayr]

What I want to do is add a column to give me a total.
But in some of the colums I have two numbers with one of these in brackets.
For example 18 (23) given me a total for that cell as 41.
If I do an auto sum it actually takes away 18 from my overall total.
But I want my total to include both numbers.
Is there an easty way round this?

 

[Yogi Anand]

What I want to do is add a column to give me a total.
But in some of the colums I have two numbers with one of these in brackets.
For example 18 (23) given me a total for that cell as 41.
If I do an auto sum it actually takes away 18 from my overall total.
But I want my total to include both numbers.
Is there an easty way round this?

Hi Jim:

I suggest you post a few rows of your sample data and the expected result ... to help viewers clearly see how your data is laid out and what you are trying to accomplish.

 

[iliace]

So let's say I have this:

A1: 1
A2: 2
A3: 3
A4: 4 (10)

This formula will result in 16 = 1+2+3+10

=SUM(IF(ISERROR(FIND("(",A1:A4)),A1:A4,--MID(A1:A4,FIND("(",A1:A4)+1,FIND(")",A1:A4)-FIND("(",A1:A4)-1)))

You have to press Ctrl+Shift+Enter after you paste it in the formula bar.

This formula will result in 20 = 1+2+3+4+10

=SUM(IF(ISERROR(FIND("(",A1:A4)),A1:A4,MID(A1:A4,FIND("(",A1:A4)+1,FIND(")",A1:A4)-FIND("(",A1:A4)-1)+LEFT(A1:A4,FIND("(",A1:A4)-1)))

Again, use Ctrl+Shift+Enter.

 

[jimboayr]

Hi iliace,

Many thanks for that it works a treat.
Just what I was looking for.


Jim

 

[jimboayr]

Hi iliace,

Many thanks for that it works a treat.
Just what I was looking for.


Jim

Came across another problem.
If both numbers in the cell are linked to another work book ie: Cell A =Bel!A1 then the number in brackets =Bas!A1. How do I basically split the cell so that the number on its own is from Bel!A1 and the number in brackets is from Bas!A1