 # 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

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

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