Forum Discussion
Solved
Combining only positive and negative numbers without using pivot data
https://docs.google.com/spreadsheets/d/1U0RxyQ4ycbXkNgGDwLDHBc4j0UG9-QIS/edit?usp=drive_link&ouid=104556168061891244163&rtpof=true&sd=true How can get the desired result as presented in the work...
in D8 (make sure E8, D9 & E9 contain nothing):
=LET( particulars, UNIQUE(Table1[Particulars]), amounts, SUMIF(Table1[Particulars], particulars, Table1[Amount]), positives, SUM( IF(amounts >= 0, amounts) ), negatives, SUM( IF(amounts < 0, amounts) ), HSTACK( {"Positives";"Negatives"}, VSTACK(positives, negatives) ) )
Thank you so much for replying.
As I can see, you have removed the need for pivot table but what I need is a single formula to be applied directly to the table in order to get the results in E8 & E9. Can you help me with that that formula??
Also, using Microsoft 365
Thanking in advance.
in D8 (make sure E8, D9 & E9 contain nothing):
=LET(
particulars, UNIQUE(Table1[Particulars]),
amounts, SUMIF(Table1[Particulars], particulars, Table1[Amount]),
positives, SUM( IF(amounts >= 0, amounts) ),
negatives, SUM( IF(amounts < 0, amounts) ),
HSTACK(
{"Positives";"Negatives"},
VSTACK(positives, negatives)
)
)- Thank you so much for the help, you have saved me a lot of time.
On reflection:
=LET( amounts, SUMIF(Table1[Particulars], UNIQUE(Table1[Particulars]), Table1[Amount]), positives, SUM( IF(amounts >= 0, amounts) ), HSTACK( {"Positives";"Negatives"}, VSTACK(positives, -positives + SUM(amounts)) ) )- Thank you for your help.
I applied the formula and got the desired result but I want to convert the result into millions, i.e., to divide the results by 100000 and that too in that single formula. I have tried to do it with your formula but getting all sort of errors. Can you please help me out here as well?
Thanking in advance
- You're welcome. Thanks for providing feedback & don't forget Excel version next time, you'll save time 🙂