Needing help to add up a column

jimboayr

Board Regular
Joined
Oct 9, 2005
Messages
120
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

Code:
=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

Code:
=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.
 
Last edited:

jimboayr

Board Regular
Joined
Oct 9, 2005
Messages
120
Hi iliace,

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


Jim
 

jimboayr

Board Regular
Joined
Oct 9, 2005
Messages
120
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,787
Members
430,569
Latest member
sbardelli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top