# & vs , in Sum Function

Topic Labels: Formulas
992 2
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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?

2 Replies 2
14 - Jupiter

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

6 - Interface Innovator

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