excelconditional-statementsformulaaddition

Adding numbers in Excel, based on a condition


What I am wanting to do is probably a straightforward issue but many, many Google searches have failed to uncover the right syntax - in Excel, I'm wanting to say something like:

'if A1 < 0, then sum columns(B1 - S1) plus A1; otherwise [ so, if A1 <=0 ], place a word instead'

At present I am trying to use a formula thus:

=IF(A1<0,(SUM(B1:S1)+A1),"credit")

So basically, a check column that should resolve to '0' for debits, and show clearly where there is a credit.

It must be possible to achieve that with a function, but I can't discover what it is - the word 'credit' appears successfully when A1 > 0, but otherwise I get nonsense.

Some version of SUMIF() perhaps, but cannot figure out its syntax. Many thanks!

Many versions of =IF() and =SUMIF() tried further to guidance I have found online - there are tens of thousands of examples of something like this but nothing that really matches.


Solution

  • Is the formula in A1 itself =SUM(B1:S1)?

    If so, changing your formula to handle the situation where A1 is negative should ensure it either resolves to 0 or returns credit, This can be achieved by swapping the '+' with a '-', see below.

    =IF(A1<0,(SUM(B1:S1) - A1),"credit")
    

    Agree with the comments of @atmo and @Solar Mike that more information on inputs and desired outputs would be useful but this seems to be a likely oversight.