# Workaround for this use of the COUNTIF function

Topic Labels: Formulas
Solved
793 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

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 😄

1 Solution

Accepted Solutions
Community Manager

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
Community Manager

@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.

4 - Data Explorer

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

Community Manager

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 - Data Explorer

Thanks @Dan_Montoya got it to work!