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 so much for the help, you have saved me a lot of time.
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 05, 2023

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

      • 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