Skip to main content
Solved

Calculating the total number of 'letters' in a record

  • November 22, 2021
  • 6 replies
  • 45 views
A
Forum|alt.badge.img+3

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.

Best answer by kuovonne

Unfortunately not returns “invalid formula”


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)
)

6 replies

Dimitris_Goudis
Forum|alt.badge.img+20

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

A
Forum|alt.badge.img+3
  • AJTech
  • Author
  • Inspiring
  • November 22, 2021

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


Unfortunately not returns “invalid formula”

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • November 22, 2021

Unfortunately not returns “invalid formula”


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)
)
ScottWorld
Hannah_Wiginton
Dimitris_Goudis
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 22, 2021

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.

Hannah_Wiginton
A
A
Forum|alt.badge.img+3
  • AJTech
  • Author
  • Inspiring
  • November 23, 2021

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)
)

Simple and elegant solution @kuovonne thank you - works perfectly :white_check_mark: :white_check_mark:

Dimitris_Goudis
Forum|alt.badge.img+20

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)
)

Thanks for the correction @kuovonne :slightly_smiling_face:

A
Terms & ConditionsAccessibility statement