Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formula to use across multiple columns?

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

Solved

Jump to Solution

0
5109
14

Showing results for

Comment Post Options

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

Apr 27, 2019 10:08 PM

I have 3 separate fields and I want the 4th field to show a result based on the 3.

The responses can be yes, no, maybe

If at least 2 of the 3 columns are YES, then result should be YES

If at least 2 of the 3 columns are NO, then result should be NO

If at least 2 of the 3 columns are MAYBE, then the result should be MAYBE

If each column has one response of each, then the result should be MAYBE

I am new to Airtable and am more familiar with Excel, so a simple formula or guidance would be helpful. My current Excel has a long formula of nested IF and COUNTIF. Are there equivalents of those on here?

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

May 01, 2019 04:23 AM

Doing this I realized that you just need to add a bunch of ‘Maybes’ in the third switches as the default option. Even you can simplify green sub-branches as ‘IFs’ because there are 2 ‘Maybes’.

Also, I noticed that I missed several commas because of the copy-pasting (now the @JonathanBowen’s solutions begins to feel better :smile_cat: ). Anyway, the important is to understand the method and the logic, and you could do it in other scenarios.

I leave the full formula, I think now it’s correct:

```
SWITCH(
'Col1',
'Y', SWITCH(
'Col2',
'N', SWITCH(
'Col3',
'Y', 'Yes',
'N', 'No',
'Maybe'
),
'M', IF(
'Col3' = 'Y',
'Yes',
'Maybe'
),
'Yes'
),
'N', SWITCH(
'Col2',
'Y', SWITCH(
'Col3',
'Y', 'Yes',
'N', 'No',
'Maybe'
),
'M', IF(
'Col3' = 'N',
'NO',
'Maybe'
),
'No'
),
'M', SWITCH(
'Col2',
'Y', IF(
'Col3' = 'Y',
'Yes',
'Maybe'
),
'N', IF(
'Col3' = 'N',
'No',
'Maybe'
),
'Maybe'
)
)
```

Also, what @Gary_Ford said is very true.

Reply

14 Replies 14

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

Apr 28, 2019 05:19 AM

Give this a look.

Basics of the IF function The IF function will return one of two values depending on whether a logical argument is true or not. Written in the format: IF([logical argument], [value1], [value2]) thi...

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

Apr 28, 2019 12:53 PM

Thank you for your response, but I have already looked at that and it doesn’t achieve what I need to do. It doesn’t seem like Airtable does cell ranges like Excel.

This is my current Excel Formula for reference of what I need to translate into Airtable formulas.

=IF(COUNTIF(C2:E2,“Yes”)=3,“YES”,IF(COUNTIF(C2:E2,“Yes”)=2,“YES”,IF(COUNTIF(C2:E2,“Maybe”)=3,“MAYBE”,IF(COUNTIF(C2:E2,“Maybe”)=2,“MAYBE”,IF(COUNTIF(C2:E2,“No”)=3,“NO”,IF(COUNTIF(C2:E2,“No”)=2,“NO”,“MAYBE”))))))

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

Apr 29, 2019 02:56 AM

Hi @lexx - Airtable doesn’t have a `COUNTIF`

function, so you can’t replicate this directly (would be great if it did!). I tried various ways to solve this problem using `FIND()`

, `MID()`

etc, but all horrible or didn’t work. The best I could come up with is a total hack and a bit flukey:

Ignoring the order of the answers I *think* you’ve got 10 possible combinations of answers:

3 Yes

3 No,

3 Maybe? (ignore the “?” for now, will explain)

2 Yes, 1 No

etc, etc

If you join these 3 columns together and then find the length of each concatenated string you get:

By chance, the lengths are different for all combinations:

So you could now do a SWITCH() function on the length to determine the answer you require:

```
SWITCH(
Length,
6, "No",
7, "No",
8, "Yes",
.
.
.
18, "Maybe?"
)
```

And this is why you need the “?” at the end of the “Maybe”. If you don’t have this, you don’t get a unique value for each combo, so the hack doesn’t work.

Obviously, this isn’t a general solution to the problem, but a very specific one (and even then, only if you can tolerate “Maybe?” instead of “Maybe”. Or “Maybe!” or “May-be”. All of them terrible, TBH :slightly_smiling_face: ).

JB

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

Apr 29, 2019 06:36 AM

```
SWITCH(
'Col1',
'Y', SWITCH(
'Col2',
'N', SWITCH(
'Col3',
'Y', 'Yes',
'N', 'No'
),
'M', SWITCH(
'Col3',
'Y', 'Yes'
'M', 'Maybe'
)
'Yes'
),
'N', SWITCH(
'Col2',
'Y', SWITCH(
'Col3',
'Y', 'Yes',
'N', 'No'
),
'M', SWITCH(
'Col3',
'N', 'NO'
'M', 'Maybe'
)
'No'
),
'M', SWITCH(
'Col2',
'Y', SWITCH(
'Col3',
'Y', 'Yes',
'M', 'Maybe'
),
'N', SWITCH(
'Col3',
'N', 'No'
'M', 'Maybe'
)
'Maybe'
)
)
```

I’ve used capital letters for values and full words for results. You can change the values and it will work always. This is the schema I’ve followed:

Reply

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

Apr 29, 2019 06:39 AM

@Elias_Gomez_Sainz Ingenious! Like it.

Reply

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

Apr 29, 2019 06:40 AM

And more generally applicable too.

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

Apr 29, 2019 06:41 AM

You were ingenious! :grinning_face_with_big_eyes: But I think the “logic approach” is more solid.

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

Apr 29, 2019 08:00 AM

Keep in mind that AirTable is not a spreadsheet it is a relational database.

Your spreadsheet mind-set is fighting you with bad information working with relational databases. Spreadsheets and relational databases are not the same animal.

In AirTable each row is a record. For example in a Contact Database each row would be a different contact and the columns would hold the contact information. Records are composed of fields(columns). Fields or ( columns) are where the data lives. Each column is set up to do only one thing. It can hold data such as a contact first name. A column can also have a formula and the formula will act on every row for that column. What happens in columns (format, formulas, whatever) is consistent for every record. You can not have a custom calculation in column(field) 3 of row(record) 6 and a different calculation in column 3 row 7. Whatever you set up a column to do is what the columns does for every record (row) in the database. This does not mean that you can not achieve the same results you can with a spread sheet, but the way you are going to organize the data is completely different. You will never get there thinking in spreadsheet mind-set. This is a completely different tool. Read the airtable section that describes relational databases. Then look at the examples. You have to understand how relational databases work so that you can understand how you need set up your data in various related databases in order to setup a structure of related data that will then let you solve your particular problem. Relational Databases are not for every problem.

Gary

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

May 01, 2019 12:35 AM