Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Combining only positive and negative numbers without using pivot data

Copper Contributor

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.

12 Replies

Hi @KunalGoel9911 

 

Welcome to your Excel discussion space! asks to provide some info. like Excel version...

 

Assumed you run Excel 2021 or 365:

Sample.png

 

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.

best response confirmed by KunalGoel9911 (Copper Contributor)
Solution

@KunalGoel9911 

 

Sample.png

 

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.
You're welcome. Thanks for providing feedback & don't forget Excel version next time, you'll save time :)

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

@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?

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?

@KunalGoel9911 See attached file

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.

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

1 best response

Accepted Solutions
best response confirmed by KunalGoel9911 (Copper Contributor)
Solution

@KunalGoel9911 

 

Sample.png

 

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

View solution in original post