Turn on suggestions

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

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: How to count number of times a word comes up 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

0
1648
0

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

Aug 27, 2020 03:42 AM

We have a weird way of keeping track how many interviews our interviewers have done.

I would like to know how to count the number of times “1” comes up in each cell across all columns from Jan 2020 - August 2020. So in my simple way of formulating it would be something that translates the following

(number of times “1” comes up in Jan2020) + (number of times “1” comes up in Feb2020) + (number of times “1” comes up in March2020) + (…etc etc).

You can access a view example here: https://airtable.com/shrVjcpeWwW1jC51n

Let me know if something is not clear! Hope somebody has some ideas or an answer :slightly_smiling_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

Aug 28, 2020 07:19 AM

You can use a bit more complicated formula to count if a string occurs multiple times:

```
LEN({January 2020}) - LEN(SUBSTITUTE({January 2020}, "1", "")) +
LEN({February 2020}) - LEN(SUBSTITUTE({February 2020}, "1", "")) +
LEN({March 2020}) - LEN(SUBSTITUTE({March 2020}, "1", "")) +
...
```

Reply

5 Replies 5

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

Aug 27, 2020 02:14 PM

Maybe a search can help? (for a temporary view and nr…)

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

Aug 27, 2020 08:00 PM

Does each search string appear a maximum of once per cell? If so, you could have a formula that searches each cell in the row and adds up the number of times it appears.

```
IF(FIND("1", {January 2020}) +
IF(FIND("1", {February 2020}) +
IF(FIND("1", {March 2020}) +
IF(FIND("1", {April 2020}) +
IF(FIND("1", {May 2020}) +
IF(FIND("1", {June 2020}) +
IF(FIND("1", {July 2020}) +
IF(FIND("1", {August 2020})
```

You can then use the summary bar to find the sum of the column of the formula field.

It looks like you are using Airtable like a spreadsheet, and Airtable can do many spreadsheet-like things. If your base design works for you, by all means use it. However, if you find yourself ready to change your base design, I suggest you look into the concept of “normalizing your data” for a database. You may find that analyzing your data becomes easier if you normalize your data. This support article might give you some ideas.

Reply

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

Aug 28, 2020 12:58 AM

Hi @kuovonne,

Thank you for response, unfortunately there is places that it comes up twice.

I totally agree the rest of our data base is normalized, but unfortunately I am not allowed to adjust this one.

Is there any way of counting it if a “1” comes up more than once?

Thanks for everyone efforts!

Solved
See Solution in Thread

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

Aug 28, 2020 07:19 AM

You can use a bit more complicated formula to count if a string occurs multiple times:

```
LEN({January 2020}) - LEN(SUBSTITUTE({January 2020}, "1", "")) +
LEN({February 2020}) - LEN(SUBSTITUTE({February 2020}, "1", "")) +
LEN({March 2020}) - LEN(SUBSTITUTE({March 2020}, "1", "")) +
...
```

Reply

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

Aug 28, 2020 07:33 AM

You are amazing thank you!