Forum Discussion
Combining only positive and negative numbers without using pivot data
How can get the desired result as presented in the workbook without using the pivot table.
I want the combined positive values in one cell and combined negative values in another cell.
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) ) )
