I WANT TO TOTAL WITHOUT ()

[Jagat Pavasia]

Dear sir,

I want to total my E8 to E26 at in E6.

I have digit in my E row with bracket.

I have inserted screen shot below.

pls help

 

[Fluff]

Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Sufiyan97]

Try

Confirm formula with Ctrl + Shift + Enter

TOTAL WITHOUT ().xlsx
	A	B	C	D	E	F
1
2
3
4
5
6					7307.4
7
8					384.60 (17/11/2022)
9					384.60 (17/11/2022)
10					384.60 (17/11/2022)
11					384.60 (17/11/2022)
12					384.60 (17/11/2022)
13					384.60 (17/11/2022)
14					384.60 (17/11/2022)
15					384.60 (17/11/2022)
16					384.60 (17/11/2022)
17					384.60 (17/11/2022)
18					384.60 (17/11/2022)
19					384.60 (17/11/2022)
20					384.60 (17/11/2022)
21					384.60 (17/11/2022)
22					384.60 (17/11/2022)
23					384.60 (17/11/2022)
24					384.60 (17/11/2022)
25					384.60 (17/11/2022)
26					384.60 (17/11/2022)
27
Sheet3

Cell Formulas
Range	Formula
E6	E6	=SUM(REPLACE(E8:E26,SEARCH("(",E8:E26),99,"")+0)

 

[Gilesth]

you could do this by using another column in excel, then you can add your sum formula. Im sure there are lots of other methods as well

 

[Fluff]

Another option

=SUM(--(LEFT(E8:E26,FIND("(",E8:E26)-1)))

 

[AlanY]

have a look if this work for you

=SUMPRODUCT(--LEFT(E8:E26,(FIND(" ",E8:E26)-1)))

 

[Jagat Pavasia]

Try

Confirm formula with Ctrl + Shift + Enter

TOTAL WITHOUT ().xlsx
	A	B	C	D	E	F
1
2
3
4
5
6					7307.4
7
8					384.60 (17/11/2022)
9					384.60 (17/11/2022)
10					384.60 (17/11/2022)
11					384.60 (17/11/2022)
12					384.60 (17/11/2022)
13					384.60 (17/11/2022)
14					384.60 (17/11/2022)
15					384.60 (17/11/2022)
16					384.60 (17/11/2022)
17					384.60 (17/11/2022)
18					384.60 (17/11/2022)
19					384.60 (17/11/2022)
20					384.60 (17/11/2022)
21					384.60 (17/11/2022)
22					384.60 (17/11/2022)
23					384.60 (17/11/2022)
24					384.60 (17/11/2022)
25					384.60 (17/11/2022)
26					384.60 (17/11/2022)
27
Sheet3

Cell Formulas
Range	Formula
E6	E6	=SUM(REPLACE(E8:E26,SEARCH("(",E8:E26),99,"")+0)

not working when i extend formula to E8:E500

 

[Jagat Pavasia]

Another option

=SUM(--(LEFT(E8:E26,FIND("(",E8:E26)-1)))

not working when i extend formula to E8:E500, please help me to extend formula to E500

 

[Fluff]

please help me to extend formula to E500

Please see post#2 & provide some data.

 

[Sufiyan97]

not working when i extend formula to E8:E500

In post #1 you did mentioned just upto row 26

Try

Book1
	A	B	C	D	E	F
1
2
3
4
5
6					7307.4
7
8					384.60 (17/11/2022)
9					384.60 (17/11/2022)
10					384.60 (17/11/2022)
11					384.60 (17/11/2022)
12					384.60 (17/11/2022)
13					384.60 (17/11/2022)
14					384.60 (17/11/2022)
15					384.60 (17/11/2022)
16					384.60 (17/11/2022)
17					384.60 (17/11/2022)
18					384.60 (17/11/2022)
19					384.60 (17/11/2022)
20					384.60 (17/11/2022)
21					384.60 (17/11/2022)
22					384.60 (17/11/2022)
23					384.60 (17/11/2022)
24					384.60 (17/11/2022)
25					384.60 (17/11/2022)
26					384.60 (17/11/2022)
27
Sheet3

Cell Formulas
Range	Formula
E6	E6	=SUMPRODUCT(IFERROR(LEFT(E8:E500,SEARCH("(",E8:E500)-2)+0,0))