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) ) )
Welcome to your Excel discussion space! asks to provide some info. like Excel version...
Assumed you run Excel 2021 or 365:
in D3
=SORT( UNIQUE(Table1[Particulars]) )
in E3
=SUMIF(Table1[Particulars], D3#, Table1[Amount])
EDIT: Forgot the totals of Positives & Negatives. See in attached file
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.
