Announcements
cancel
Showing results for
Did you mean:
Creator III

## Using COUNT and BETWEEN function together

Hello all,

Good morning! I would like to use COUNT and BETWEEN function together. I am trying to count the total number of questions and apply another condition. For example,

if(count(Questions)>"=4 <=7", if(Count({<Marks = {'10'}>}Questions)>=3, if(Count({<Marks = {'7', '8', '9', '10'}>}Questions)>3, 'Excellent', 'Good.'

I wanted to say if the total number of questions are between 4 and 7, and

if >3 questions have more than 10 marks,

and if the rest all the questions have more than 7 marks, say 'Excellent', otherwise 'Good'.

Two questions:

1. The above function does not work, and it seems that the problem lies in the first IF and between,  i.e., [if(count(Questions)>"=4 <=7"]. If I apply if(count(Questions)>=3, it works.

2. The third IF statement, i.e., if(Count({<Marks = {'7', '8', '9', '10'}>}Questions) works fine, but how can I count the marks that are more than 7? Rather than saying Marks={'7', '8', '9', '10'}. When I gave {>7}, it does not work.

t doesn't stop there, I will have to write this for questions less than 4, and questions more than 7 but if I can get the first part right, I can build the rest.

13 Replies

You'll have to split that into two conditions with an AND:

if(count(Questions)>4 AND  count(Questions)<=7, ....

To count the mark larger  than or equal to 7 use:
if(Count({<Marks = {'>=7'}>}Questions)

talk is cheap, supply exceeds demand
MVP

If what Gysbert‌ has shared with you doesn't solve your problem, would you be able to share a sample data with your expected output??

Creator III
Author

Thanks Gysbert for the quick reply!

Hi Sun, I will try and work on it and put the sample if it doesn't work. Thanks again!

Creator III
Author

Hi Gysbert and Sunindia,

The syntax seems to be working but not getting the desired output. I am using the following expression and I am sure by looking at the script, you will be able to ascertain what I am trying to get. I have attached the files too.

=if(count(Questions)>=10 AND  count(Questions)<20, if(Count({<Marks = {'10'}>}Questions)>=2, 'Excellent',
if(count(Questions)>=20 AND  count(Questions)<40, if(Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',
if(count(Questions)>=40 AND  count(Questions)<60, if(Count({<Marks = {'10'}>}Questions)>=6, 'Excellent',
if(count(Questions)>=10 AND  count(Questions)<20, if(Count({<Marks = {'>=7 <=9'}>}Questions)>=3, 'Good',
'Insufficient'))))))))

The script will be much longer than this and I will have to include multiple combinations, but I thought I would start with this. Not sure what I am doing wrong. Any help is much appreciated. I want the result to look something like the below.

MVP

You need to change your if statement to something like this:

=if(count(Questions)>=10 AND  count(Questions)<20 and Count({<Marks = {'10'}>}Questions)>=2, 'Excellent',

if(count(Questions)>=20 AND  count(Questions)<40 and Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',

if(count(Questions)>=40 AND  count(Questions)<63 and Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',

if(count(Questions)>=10 AND  count(Questions)<20 and Count({<Marks = {'>=7 <=9'}>}Questions)>=3, 'Good',

'Insufficient'))))

Specialist

Find the attached App

Creator III
Author

Thanks Sunindia, I will try that and let you know.

Creator III
Author

Hi,

I think you missed the attachment. Besides, I am using the developer edition, so I would appreciate if you could post the expression.

Thanks!

Creator III
Author

Hi all,

It works, which is great! However, I am confronted with another issue. The rule says:

If I have 4 to 8 questions, and if 2 or more has 10 marks and ALL THE REST of the questions has marks between 7 and 9, say 'Excellent', otherwise 'Good'.

The first and second part is fine which is:

If(count(Questions)<=3 AND count(Questions)<=8 AND count({<Marks = {'10'}>}Questions)>=2 AND ...

not sure how to write the last part to say...if the REST OF THE QUESTIONS have marks between 7 and 9, say 'excellent', or 'good'.

I thought of adding, count({<Marks = {'>7 <9'}>}Questions)">=5 <=8", 'Excellent', 'Good') but not sure if I am doing the right thing.

Community Browser