Formulas within a Cell

[deschroeder]

Looking for a solution to total amounts from a range of cells with or without formulas that produce a net result.

Example:

   Column A (rows of cells depicted below)

1  100.00

2    50.00 (100.00-50.00)

3    25.00 (50.00-25.00)

4   125.00 (150.00-25.00)

5   300.00 <-Total but actual amounts are +400.00 and -100.00

Numbers in parenthesis represent formulas within cells A2, A3 & A4.

Need a solution, without an additional column, to sum the positive amounts apart from the negative amounts.

Thank you.

[#2]

@deschroeder 

Could you please add an example file (without sensitive data)

how exactly it should be classified in the sheet and where you want which result.

Excel version and operating system would be an advantage to come up with a quick solution proposal.

Here is a small example (insert file).

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

I'm not seeing an option to include an attachment...

[#4]

Figured it out.

[#5]

@deschroeder 

As variant

Anvances:
=LET(
 range, A1:A4,
 str, SUBSTITUTE( FORMULATEXT(range), "=", "" ),
 pos, FIND( "-", str ),
 SUM( IFNA( --LEFT( str, pos -1 ), range ) ) )

Payments:
=LET(
 range, A1:A4,
 str, SUBSTITUTE( FORMULATEXT(range), "=", "" ),
 pos, FIND( "-", str ),
 -SUM( IFNA( --RIGHT( str, LEN(str) - pos ), 0 ) ) )

[#6]

@Sergei Baklan 

Thank you for your solution.

Can this be accomplished using existing Excel functions without using VBA?

[#7]

@deschroeder 

That's not VBA, that's existing LET function (microsoft.com) . Availability depends on your Excel version and platform.

[#8]

OK, I see that now, I'm a version behind but will upgrade and put your solution into practice. Thank you!

[#9]

@deschroeder , you are welcome

[#10]

My Excel version is now updated and I have access to the LET function, I need to add date parameters to filter to range of cells that may or may not have a subtraction formula within the cell. Can you provide an example of how to add these additional criteria into the formula you previously provided? Thank you.