Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- & vs , in Sum Function

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
1507
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 12, 2019 12:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 12, 2019 12:57 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 13, 2019 05:41 AM