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
- Workaround for this use of the COUNTIF function

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

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

Feb 06, 2024 07:38 AM

Hi everyone, first of all I'm really sorry for the noob question, I'm an English teacher who is not good at math and I cannot figure this out after days of trying.

I have an excel document where tasks are given scores based on prioritization questions.

Most of the questions follow a simple "If answer = YES add a numerical value of 3 to the overall score" format.

But I have a few segments that use COUNTIF logic.

In one of the sections where there are 5 prioritization questions the COUNTIF logic looks like this

(COUNTIF(I7:M7,"Yes")>3,12,IF(COUNTIF(I7:M7,"Yes")>2,9,IF(COUNTIF(I7:M7,"Yes")>1,6,3))

So the yellow section of questions follows:

If 1 question in the yellow segment is answered "yes" - add 3 points to the overall score

If 2 questions in the yellow segmentare answered "yes" - add 6 points to the overall score

If 3 questions in the yellow segment are answered "yes" - add 9 points to the overall score

If 4 or 5 questions in the yellow segment are answered "yes" - add 12 points to the overall score

And this would add a numerical value of 12 to the overall score

Is there anyway to recreate this COUNTIF functionality in Airtable?

I'd really appreciate any help that comes my way 😄

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

Feb 07, 2024 05:43 AM

for questions 4 and 5 you have a formula that says

`IF(OR({question 4} = "yes", {question 5}="yes"), 12, 0)`

for the 2nd yellow section a quick way to do that would be to do a similar process for questions 1-3

use 3 columns with :

```
IF({Question 12}="Yes", 1, 0)
IF({Question 13}="Yes", 1, 0)
IF({Question 14}="Yes", 1, 0)
```

And then a 4th that add those together

and a new field with the if statement

```
IF({question 12} + {question 13} + {question 14}>2, 12,
IF({question 12} + {question 13} + {question 14}> 1, 6, 0)
```

You could do this more elegantly with fewer fields with a long complex series of statements. I like to keep them simple for debugging so I can debug individual sections.

4 Replies 4

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

Feb 06, 2024 02:04 PM

@Nate , I find it helpful to break them out into small chunks for people new to airtable.

Create a column called "yellow questions score" use a formula like:

`IF({Question 1}="Yes", 3, 0)`

repeat for for all your questions then total all the scores.

You can reduce the number of score columns by creating a more complex IF() statement that does the scoring.

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

Feb 07, 2024 04:46 AM

Hi @Dan_Montoya thanks for that.

That is the formula I have for the questions that can be scored individually.

Maybe I'm not explaining it properly But what I'm looking for is a way to give a score for any combination amount of "YES". A simple +3 for a "YES" would be fine if it wasn't for

"If 4 or 5 questions in the yellow segment are answered "YES" - add 12 points to the overall score"

I've created a sheet to demonstrate, the yellow sections have the COUNTIF functions that I'm speaking about.

The first yellow section is:

If 1 question in the yellow segment is answered "yes" - add 3 points to the overall score

If 2 questions in the yellow segmentare answered "yes" - add 6 points to the overall score

If 3 questions in the yellow segment are answered "yes" - add 9 points to the overall score

If 4 or 5 questions in the yellow segment are answered "yes" - add 12 points to the overall score

And this would add a numerical value of 12 to the overall score

The second yellow section is:

Two or more "YES" in this section - add 6 points to the overall score

One "YES" from this section - add 3 points to the overall score

I hope that helps, I feel like I'm bad at explaining

This browser version is no longer supported. Please upgrade to a supported browser.

Solved
See Solution in Thread

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

Feb 07, 2024 05:43 AM

for questions 4 and 5 you have a formula that says

`IF(OR({question 4} = "yes", {question 5}="yes"), 12, 0)`

for the 2nd yellow section a quick way to do that would be to do a similar process for questions 1-3

use 3 columns with :

```
IF({Question 12}="Yes", 1, 0)
IF({Question 13}="Yes", 1, 0)
IF({Question 14}="Yes", 1, 0)
```

And then a 4th that add those together

and a new field with the if statement

```
IF({question 12} + {question 13} + {question 14}>2, 12,
IF({question 12} + {question 13} + {question 14}> 1, 6, 0)
```

You could do this more elegantly with fewer fields with a long complex series of statements. I like to keep them simple for debugging so I can debug individual sections.

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

Feb 07, 2024 08:03 AM

Thanks @Dan_Montoya got it to work!