Forum Discussion
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.
What's happening with this formula is that it's including the zeroes as well, i.e., including the zeroes in positives and even if I remove the "=" sign from the formula, it's including the zeroes in negatives because the formula for negatives is "Total-positives", I want to exclude zeroes from this equation. Can you please help?
Also, you have combined the formula for counts and sums but I wanted them separately, can you help me with that as well?
Thanking in advance.
What's happening with this formula is that it's including the zeroes as well, i.e., including the zeroes in positives and even if I remove the "=" sign from the formula, it's including the zeroes in negatives because the formula for negatives is "Total-positives", I want to exclude zeroes from this equation. Can you please help?
Also, you have combined the formula for counts and sums but I wanted them separately, can you help me with that as well?
Thanking in advance.
Sounds like a never ending story 🙂 Instead of text could you please upload a representative sample of your data + the expected result? Thanks
