Forum Discussion

dsdans's avatar
dsdans
Copper Contributor
Jun 22, 2025

Excel IF/IFS formula not calculating scenario "B"

Hi, Hopefully an easy solution that's staring me in the face, but I've been trying to fix this for an hour and I'm getting nowhere. Any pointers appreciated.   I've been working on a new calculato...
excel
Formulas and Functions
m_tarler's avatar
m_tarler
Bronze Contributor
Jun 24, 2025

what is in S15?  that is the only difference.  I'm surprised it returns blank as opposed to some sort of error.

also try using SUM this way:

=IF(S9="YES",(N52+N57)*Z18, SUM(N52,N57)-SUM(S15:S20,S22,S43,S49,T64))

because SUM(N52+N57) really isn't using the SUM function because it will add N52+N57 and then apply the SUM() to a single digit.  Similarly I applied the SUM() to the other list and took advantage of referencing a range of cells S15:S20 instead of adding each individually.  The ability to add a range of cells is one of the advantages of using SUM().  Another is that SUM() will ignore text values and not throw an error.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 24, 2025

It depends. If in N52 and N57 we have "1" and "2" (not 1 and 2), when SUM(N52+N57) gives 3. SUM(N52,N57) returns zero.

Without file or at least screenshot that's only guesses.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 24, 2025

    that is an excellent point that TEXT that can be interpreted as numbers will be evalutaed using + while SUM will ignore.  But I still don't see a case for having both.  how is SUM(N52+N57) any different than N52+N57

Resources