Combining only positive and negative numbers without using pivot data

[KunalGoel9911]

https://docs.google.com/spreadsheets/d/1U0RxyQ4ycbXkNgGDwLDHBc4j0UG9-QIS/edit?usp=drive_link&ouid=10... 

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.

[#2]

Hi @KunalGoel9911 

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

[#3]

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.

[#4 · Accepted solution]

@KunalGoel9911 

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)
  )
)

[#5]

Thank you so much for the help, you have saved me a lot of time.

[#6]

You're welcome. Thanks for providing feedback & don't forget Excel version next time, you'll save time 🙂

[#7]

@KunalGoel9911 

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))
  )
)

[#8]

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

[#9]

@KunalGoel9911 

I want to convert the result into millions, i.e., to divide the results by 100000. That would be:

SUMIF(Table1[Particulars], UNIQUE(Table1[Particulars]), Table1[Amount]) / 10^5

or I misunderstood something?

[#10]

Yes that is totally correct. You have been of immense help.

Just one last question, instead of sum, if I want the results to be presented in counts, i.e., all unique positives like A & D & so on must be counted as "1(A) + 1(D) + all unique postives" and same goes for negatives.

Meaning, I want to know that how many unique letters make up the sum of positives and negatives.

Can you help me with the formula for that?

[#11]

@KunalGoel9911 See attached file

[#12]

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.

[#13]

Hi @KunalGoel9911 

Sounds like a never ending story 🙂 Instead of text could you please upload a representative sample of your data + the expected result? Thanks