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

Resources