How to use aggregate functions to summarise repeats with a condition?

07-16-2021 04:29 AM
Occasional Contributor III

I have a survey recording types of litter found during litter picks. We added a repeat field to the end of the survey for users to record items that were not listed in the categories provided on their tally sheets. In this repeat, the users are asked to provide:

  • An item description (e.g. sunglasses) - text field
  • The material type (e.g. plastic)  - select one field
  • And the total number of these items found (e.g. 2) - integer field

I can use the aggregate functions to calculate:

  • The total number of items counted: sum(${Total_count})
  • The number of different item types: count(${Item_description})

But I can't seem to be able to calculate the total number of items counted by material type. I have tried using an if statement:

if(selected(${Material_type}, 'Plastic'), sum(${Total_count}), 0)

But the value always returns 0, even when the statement is true. Is this possible within Survey123?

Anneka France
Technical Manager at The Rivers Trust
1 Reply
New Contributor

Hello Anneka_France,

This is an idea, I used the JS capabilities of survey123 to count items in an Array based on material.  Here is an example code:

function plasticCounter(materialArray){

let plastic = 0;

for(let i =0; i < matArray.length; i++){

if(matArray[i] = "plastic"){ plastic++;}


return plastic;

Important Notes:

1. The code has lots of room for improvement for example you can make a function to handle all of your materials and also pass the material filter.

2. there are also other issues that will come up with this for example, what happens when someone deletes a value from the repeat you would need to handle that in your material count.

3. I am positive you can achieve this logic without the use of JS(which would be better).

4. This is not meant to work within the repeat.

Again, this is a rough draft hope it gives you some ideas, and yes! this is possible within S123

0 Kudos