Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Workaround for this use of the COUNTIF function

Topic Labels: Formulas
Solved
Jump to Solution
2141 4
cancel
Showing results for 
Search instead for 
Did you mean: 
natetable
4 - Data Explorer
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))

Screenshot 2024-02-06 at 15.25.37.png

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

Screenshot 2024-02-06 at 15.25.54.png

Is there anyway to recreate this COUNTIF functionality in Airtable?

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

1 Solution

Accepted Solutions
Dan_Montoya
Community Manager
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.

See Solution in Thread

4 Replies 4
Dan_Montoya
Community Manager
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.

 

 

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

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

This browser version is no longer supported. Please upgrade to a supported browser.
Dan_Montoya
Community Manager
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.

natetable
4 - Data Explorer
4 - Data Explorer

Thanks @Dan_Montoya got it to work!