The SUM function is not returning the expected result when values are input directly into the function.

Question

Description: When using the SUM function, the output does not match the expected sum of the provided inputs. Additionally, calculations based on certain conditions yield different values than anticipated.

Steps:

1. condition mentioned inside the sum function =SUM(200<300,300<400) - Output is giving as 2
2. condition mentioned other namebox like A1 = 200<300 output: TRUE, A2 = 300<400 output : TRUE
3. added formula like =SUM(A1,A2) - Output is giving as 0.

Excel Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20114) 64-bit

Answer 1

If you pass a single literal data value as an argument to SUM it will try to coerce it to a number. So, just as in your example, if you used =SUM(TRUE,TRUE) it would return 2 because each TRUE is a single literal that can be coerced to a number (1). Similarly, =SUM("1","2") will return 3.

If, however, you pass cell references containing those values, or pass them as one array like this:

=SUM({TRUE,TRUE})

or

=SUM({"1","2"})

then no coercion will occur.

If you are asking why that is the case, then you'd probably need to talk to one of the Excel programmers.