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.
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.
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.
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
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.
Feb 07, 2024 08:03 AM
Thanks @Dan_Montoya got it to work!