Forum Discussion
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.
9 Replies
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 ) ) )Thank you for your solution.
Can this be accomplished using existing Excel functions without using VBA?
That's not VBA, that's existing LET function (microsoft.com) . Availability depends on your Excel version and platform.
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)
- I'm not seeing an option to include an attachment...
- Figured it out.
