SOLVED

# Combining only positive and negative numbers without using pivot data

Copper Contributor

# 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.

12 Replies

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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?

# Re: Combining only positive and negative numbers without using pivot data

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?

# Re: Combining only positive and negative numbers without using pivot data

@KunalGoel9911 See attached file

# Re: Combining only positive and negative numbers without using pivot data

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.

# Re: Combining only positive and negative numbers without using pivot data

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

# Re: Combining only positive and negative numbers without using pivot data

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