# Formulas within a Cell

Occasional Contributor

# Formulas within a Cell

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.

8 Replies

# Re: Formulas within a Cell

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)

# Re: Formulas within a Cell

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

Figured it out.

# Re: Formulas within a Cell

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 ) ) )

# Re: Formulas within a Cell

Thank you for your solution.

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

# Re: Formulas within a Cell

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

# Re: Formulas within a Cell

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

# Re: Formulas within a Cell

@deschroeder , you are welcome