# Something like COUNTIF in XLS / Kobo

Hello,

I am trying to use count-selected score a survey response, but I’d like one potential selection of a select_multiple question to be excluded from that count. In Excel we’d usually be using COUNTIF, but I was curious how I could recreate that in a Kobo calculation. Can someone help me figure out how to exclude one potential selection of a select_multiple question from the final count calculation? I’m wanted to attach the Excel sheet for reference, but I am a new user and am not permitted to upload documents yet.

Can someone give me a hand?

Welcome,
An idea:
You could use count-selected and additionally use selected(…) for the choice you want to exclude to see if you must substract 1. That is:
count-selected(… ) - if(selected(…, …), 1, 0)
Kind regards

Thanks for this response. I hate to be a bother, but I may need it explained out a bit more clearly. It’s a pity I can’t share the Excel I’m working on. Essentially, there are five potential responses, and I’d like how many are ticked to be counted, except the first response. I tried to carry this out based on your guidance, but still am a bit lots. Might you be able to explain a bit more? Thanks again for reading and responding!

Hi @nmuser,

You should now be able to share your xlsform in the community! Would you mind sharing them in detail so that we could provide you the best possible solution for your issue.

Have a great day!

Thank you!

So, I’ve attached the XLS form. Essentially. if the first selection is chosen, I’d like the note to read"5", if any other three choices are selected, the note should read “1”, if any two choices are made the note should read “2”, and if one choice is made (but not the first one ‘No Assistance’) the note should read “3”. So, I’m trying to figure out a way to count everything but the first selection “No Assistance”. Thanks all for your inputs so far and hopefully looking at the XLS form attached will help explain what I am getting at! testd9.xlsx (10.0 KB)

Hi @nmuser,

Would you mind describing your issue in a pictorial form so that we better understand and help provide you with the response you are seeking. Sorry but at the moment, your descriptions still seems unclear.

Have a great day!

Thanks for the feedback. I am attaching images which explain more. So, if the selection ‘No Assistance’ is made, I’d like the note (circled in red) to read ‘5’. (Image 1) If any three or more selections are made, the note should read ‘1’. (Image 2). This part seems to be working. If any two choices are selected, the note should read ‘2’, (Image 3). This part also seems to work so far. Finally if one choice is selected, the note should read ‘3’ provided that the one choice selected is not ‘No Assistance’. (Image 4). So, that last condition is where it’s not working. Right now, if ‘No Assistance’ is selected, it gives a note value of three, but I need that selection to produce a note that indicates ‘5’. This is why I was trying to find a workaround like COUNTIF, because the mistake comes from ‘No Assistance’ selection being counted at equal value than the rest of the options. Does this clarify sufficiently in combination with the XLS form?

testd9.xlsx (10.0 KB)

1 Like

Hi @nmuser
Thanks for clarifying. I looked at your form and recommended a much shorter solution that uses few questions but does what

You will notice the section that does the trick for you uses the function not selected with the if command. I must say there is still a much shorter way of doing it but could get messy, so will stick to this for now. The formula I applied is here

(if((count-selected(\${domain9}))>=“3”,1,0))+(if((count-selected(\${domain9}))=“2”,2,0))+(if((count-selected((\${domain9}))=“1” and not(selected(\${domain9},‘0’))) ,3,0))+(if((count-selected((\${domain9}))=“1” and selected(\${domain9},‘0’)) ,5,0))

I also added on the domain9 question a constraint that checks to ensure that when you choose no assistance you cannot chose anything else; this controls for the behavior you may have wanted in the form.

. = ‘0’ or not(selected(.,‘0’))

You can access the from created here Count_Selected_Formula.xlsx (11.0 KB)

With the behaviors you expected shown in the below images. Hope this helps
Nothing selected

No Assistance Selected

When you try selecting no assistance with something else: Note you can adjust your message to say "You cannot select No Assistance with something else.

One option selected

2 Options selected

3 options and above selected

Stephane

1 Like

Wow! Thanks for the detail included. This works perfectly now. It was a valuable lesson in these types of questions as they appear often. I appreciate you support and that of the whole forum so much. Thanks again!

2 Likes

Hello @nmuser
Based on @stephanealoo’s hints, another idea for solution, using cascading IFs.
(Differences are in the blue cells.)
Count_Selected_Formula_02.xlsx (11.6 KB)
Side-notes:

• Maybe the choices question should be required = true (as NO Assistance is an option)
• If nothing is selected your count will also be 0. (Could be adapted to have it empty)
• required = false is KoBo default (so not needed to specify)
• Sorry, I am not sure if this score is very meaningful, as for ex. (sec.) Education (in-kind) + Other = 2, but also Food + Healthcare = 2. A multiple choice analysis/tabulation might allow more insight? (But this is up to YOU.)
Kind regards
1 Like

Thanks to you both. Two great solutions.

2 Likes