This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

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
- Re: Calculating the total number of 'letters' in a...

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

Solved

Jump to Solution

4
1702
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

Nov 21, 2021 11:47 PM

Its probably a simple formula…but here goes…

I have a record in airtable that looks like this:

The A, B, C represent the responses to survey

What I want to do is COUNT the number of each letter - so for example

A = 3

B = 1

C = 2

E = 1

I have got as far as: IF({Col1}=‘A’,1,0) and that returns the 1 (in this example)

What does the formula look like to check in Col2 - Col7 contains ‘A’ and then give me the total of 3?

I hope that explains what I am trying to do clearly :grimacing: :nerd_face:

Thanks in advance.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

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

Nov 22, 2021 02:23 PM

Dimitris’s formula has some curly braces out of order and his field names do not match your base. Here is his formula rewritten with the curly braces in the proper place and field names/values changed to match your screen shot. Note that you will still need to extend the formula to include all the desired columns.

```
SUM(
IF({Activity 1}='A',1,0),
IF({Activity 2}='A',1,0),
IF({Activity 3}='A',1,0)
)
```

Reply

6 Replies 6

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

Nov 22, 2021 12:39 AM

Hey @AJTech,

You try the following in Total (A):

```
sum(if({Col1='a',1,0}),if({Col2='a',1,0}),if({Col3='a',1,0}))
```

Does it work for you?

Yours sincerely,

Dimitris Goudis

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

Nov 22, 2021 02:12 PM

Unfortunately not returns “invalid formula”

Solved
See Solution in Thread

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

Nov 22, 2021 02:23 PM

Dimitris’s formula has some curly braces out of order and his field names do not match your base. Here is his formula rewritten with the curly braces in the proper place and field names/values changed to match your screen shot. Note that you will still need to extend the formula to include all the desired columns.

```
SUM(
IF({Activity 1}='A',1,0),
IF({Activity 2}='A',1,0),
IF({Activity 3}='A',1,0)
)
```

Reply

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

Nov 22, 2021 02:30 PM

Here is another approach that will make it easier of you end up changing the number of activities.

Have an {AllResponses} formula field that concatenates the values of all the activity responses into one field

```
CONCATENATE(
{Activity 1},
{Activity 2},
{Activity 3},
{Activity 4},
{Activity 5},
{Activity 6},
{Activity 7}
)
```

Then have different fields that counts the number of times a particular letter appears in that formula field.

```
LEN({AllResponses}) - LEN(SUBSTITUTE({AllResponses}, "A", ""))
```

This way if you add/remove activities, you only need to edit the {AllResponses} formula.

Reply

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

Nov 22, 2021 04:41 PM

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

Nov 24, 2021 01:41 AM

Thanks for the correction @kuovonne :slightly_smiling_face:

Reply