If formula

Dear Sirs …. Kindly need your help with below table to distinguish between + or - numbers in the result cell to take + value cell keep in mind the following If formula used in result cell ( if there...

microsoft 365

PeterBartholomew1

Jul 23, 2023

Alternative

= LET(
    s, BYROW(data, LAMBDA(d, MAX(SUM(d), 0))),
    VSTACK(s, SUM(s))
  )

Riny_van_Eekelen

Platinum Contributor

Jul 21, 2023

Yousef70 Try this:

=MAX(0,SUM(O18:P18))

The above formula returns the higher number of 0 (zero) or the sum of O18:P18. So, if the sum is negative it returns 0, otherwise the sum. Format all cells with three decimals to achieve the end result shown in your example.

Your formula uses nested IF functions that are not needed and there are several issues.

=IF(SUM(O18:P18)>=0,SUM(O18:P18),IF(SUM(O18:P18)<=0,"0.000",(IF(SUM(O18:P18)<=0,O18+P18))))

The first IF checks if the sum is >=0. If that is not the case the sum is less then zero. No need to add another IF that checks if that is indeed the fact. And then the last IF never comes into play and even if it would I believe you would to change <=0 to >=0. But, as said it's irrelevant.

Then, the part "0.000" returns a text string. Probably not something you would want.

letsdnce34

Occasional Reader

Jul 08, 2026

This helped me a lot with something I was working on but I need the opposite. I need it to return to another field the amounts if they are less than zero. Ideas?

so for example if a2 and a3 >0 they go into field a7 but if they are less than zero they go into field a9

Forum Discussion

If formula