Calculate value in brackets

[Pastafarian]

Hi all,

I have multiple rows with products amounts in each cell. I want the notation to be as follow: x(+y) , where x is the paid number of products and y the amount of free products. I need just the first number to calculate the total price but for calculating the total amount of products needed I need to have both.

I tried to separate both values, but with no succes.

Anybody has some code / knows how to accomplish this ? (or a better way to get the same result (With distinction between paid and free products))


Thanks!

 

[SteveO59L]

Try posting a sample of your data

 

[Pastafarian]

Order1:-------4-------5(+1)-------6-------8(+2)-------Total paid: 23
Order2:-------3-------6-----------5(+1)---4(+3)-------Total paid: 18
Order3:-------1(+1)---2(+2)-------3-------4-----------Total paid: 10

Totaal: 9 16 15 21


Hope this helps.

 

[adam087]

I believe this will do what you need. Change references to suit.

Excel 2010
	A	B	C	D	E	F	G	H
1		Product 1	Product 2	Product 3	Product 4	Free	Paid	Total Items
2	Order 1	4	5(+1)	6	8(+2)	3	23	26
3	Order 2	3	6	5(+1)	4(+3)	4	18	22
4	Order 3	1(+1)	2(+2)	3	4	3	10	13
5	Free	1	3	1	5
6	Paid	8	13	14	16
7	Total Items	9	16	15	21
Sheet1

Cell Formulas
Range	Formula
H2	=G2+F2
B7	=B5+B6
B5	{=SUM(IFERROR(VALUE(MID(B2:B4,FIND("(",B2:B4)+1,FIND(")",B2:B4)-FIND("(",B2:B4)-1)),0))}
B6	{=SUM(IFERROR(VALUE(LEFT(B$2:B$4,FIND("(",B$2:B$4)-1)), B$2:B$4))}
F2	{=SUM(IFERROR(VALUE(MID(B2:E2,FIND("(",B2:E2)+1,FIND(")",B2:E2)-FIND("(",B2:E2)-1)),0))}
G2	{=SUM(IFERROR(VALUE(LEFT(B2:E2,FIND("(",B2:E2)-1)), B2:E2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Regards
Adam

 

[Pastafarian]

It workes like a charm! Thanks a lot, could've never come up with this myself.

 

[adam087]

No problem; glad to help. Thanks for the feedback.

Regards
Adam

 

[Pastafarian]

So this solution was working perfectly for quite some time, but now I have to SUM only the visible ones. I've tried to use Subtotal, but with no succes. Is there a way to both use Subtotal (or some other function) and at the same time have this complicated SUM still working properly? THanks!