Workaround for this use of the COUNTIF function

[natetable]

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 😄

[Dan_Montoya]

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

[natetable]

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

https://docs.google.com/spreadsheets/d/1ENKaWgVcKL5iq1QyJ-l-4W7k-keZIryx_txixj-D4Ho/edit#gid=1343567...

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

[Dan_Montoya · Accepted solution]

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.

[natetable]

Thanks @Dan_Montoya got it to work!