Skip to main content
Solved

How to count number of times a word comes up across a different columns?

  • August 27, 2020
  • 5 replies
  • 59 views
E

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.

Best answer by kuovonne

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!


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", "")) +
...

5 replies

C
Forum|alt.badge.img+9
  • Chen_Pro
  • Known Participant
  • August 27, 2020

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • August 28, 2020

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.

Justin_Barrett
E
  • EYES_Internatio
  • Author
  • Participating Frequently
  • August 28, 2020

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.


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!

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • August 28, 2020

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!


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", "")) +
...
E
Justin_Barrett
E
  • EYES_Internatio
  • Author
  • Participating Frequently
  • August 28, 2020

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", "")) +
...

You are amazing thank you!

Terms & ConditionsAccessibility statement