Forum Discussion

KunalGoel9911's avatar
KunalGoel9911
Copper Contributor
Dec 05, 2023
Solved

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...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 05, 2023

    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)
  )
)
KunalGoel9911's avatar
KunalGoel9911
Copper Contributor
Dec 05, 2023
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
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 05, 2023

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?

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 07, 2023

    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

  • KunalGoel9911's avatar
    KunalGoel9911
    Copper Contributor
    Dec 06, 2023
    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.
  • KunalGoel9911's avatar
    KunalGoel9911
    Copper Contributor
    Dec 05, 2023
    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?

Resources