& vs , in Sum Function

Hi

I made two columns (1 for current and 1 for complete) to count how many projects are linked to each contact I have. When I made the formula with an & it added 2+0=20?

When I changed the & to a , it is now calculating the correct amount 2+0=2

Why is this?

Since SUM() is a function, you have to pass it parameters to act on (the arguments you give it inside the parenthesis). The SUM() function takes each parameter and adds it to the total of all the parameters before it. A function expects its parameters to be separated by commas – it knows it has reached the end of one parameter and the beginning of the next parameter when it hits a comma.
So…

SUM(2) = 2

There is only one parameter - there are no other parameters to add to 2.

SUM(2, 2) = 4

There are two parameters, so the SUM() function knows to take the first parameter, and add the next parameter it finds to that, which it knows it has found when it reaches a comma in the parameters.

SUM(2, 2, 2) = 6

There are three parameters - the SUM() function knows to take the first parameter, add the next parameter it finds to that, keep the total, and then add the next parameter it finds to that total.


Now the next part – why does & do what it did there…

2 & 0 = "20"

& is called the “Concatenation Operator”. It is used to concatenate strings together, or “join” them together. So in normal usage, it looks like this:

"This is " & "a string." = "This is a string."
if field {Project Name} = "Docker Port"
and field {Technician} = "Jim Daly"
then
-----
{Project Name} & " - " & {Technician} = "Docker Port - Jim Daly"

Those are all examples with strings - the concatenation operator works on strings. You told the concatenation operator to work on numbers:

2 & 0 = 20

What happened is the concatenation operator coerced your numbers into strings. It knows that it can’t do math - it’s a string operator, not a math operator - so when you gave it numbers, it said “I can’t do anything with those numbers… but I can make them into strings, and concatenate them!”. So it turned your equation above into:

"2" & "0" = "20"

or

{Current} & {Complete} = "20"

When you put that inside of a SUM() function, you are only giving the SUM() function one parameter to work on – 2 & 0 is the entire parameter, because there is no comma to tell the SUM() function that there is another parameter to add to the first. So the SUM() function effectively said, “I have nothing to do here because I can’t add a single parameter to itself, so I’ll just step out and let the concatenation operator do its thing…”. So, your formula ends up as:

SUM("2" & "0") = "20"

or

SUM({Current} & {Complete}) = "20"

because the SUM() function did nothing at all, and the concatenation operator turned your numbers into strings, and concatenated them, as it is supposed to do.

What you want is:

SUM(2, 0) = 2

or

SUM({Current}, {Complete}) = 2

Hope that helps you understand all of that in a way that allows you to better use Airtable formulas

3 Likes

Certainly. Thank you very much for the amazing description on why it did it what it did. Two thumbs up!