Needing help to add up a column

jimboayr

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,543
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
118
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
118
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,865
Members
414,409
Latest member
FloordAlex

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