Sum formula in form

Dear friends, good night
i have a form which need sum in a column, and i can not fined the type of question for this, the issue is
q1: No of girls received books
q2 No of boys received books
Q3: total students received books (q1+q2)
so in Q3, what should i select in type column of survery sheet, and how to go forward?
looking forward for your instruction

You are looking for a question type "calculate" which looks like this:

+-----------+------+----------------+-------------+
|   type    | name |     label      |  calculate  |
+-----------+------+----------------+-------------+
| integer   | q1   | N° Books boys  |             |
| integer   | q2   | N° Books girls |             |
| calculate | q3   |                | ${q1}+${q2} |
+-----------+------+----------------+-------------+

Check here for more information about it:
http://xlsform.org/#calculation

2 Likes

It would be helpful if you could post the actual XForm XML of your form, so that we can deduce precisely what the data looks like. But making the assumption that you have two number control/properties in your XForm - say /data/numgirlswithbooks and /data/numboyswithbooks - then you could do it by specifying a simple calculate() in a binding for the /data/total output control:

<bind nodeset='/data/total' calculate='number(/data/numgirlswithbooks) + number(/data/numgirlswithbooks)' type='integer'>

Note: this assumes numgirlswithbooks and numboyswithbooks is always set (eg to 0) and never left blank. Otherwise, if either can be blank then you'll need a bit more XPath stuff in the calculation, because number('') will return NaN, which will render the entire total result NaN...

1 Like

exactly dear Jruizc, than you so much

Thank you xiphware, but i have not information in xml file development

A post was split to a new topic: Error occurs during form loading

Dear, All

I have a question of the same topic.
How to calculate the sum .e.g fruits
q1 do you like bananas
1 0
put to 5 questions.
So what I want is that if someone choose 1 in all 5 questions the result calculation should be 5,if 0 in all questions, the result calculation should be 0.Fruits.xlsx (7.9 KB)

Hi @smtengula

you need something like this: calcFruits.xlsx (20.9 KB)

1 Like

Hi, Grzesiek2010

Thanks alot, this what I was looking for.

Thanks

I have a followup question on this issue since I am also looking for something like that. What if there are three choices (yes, no and NA) and I dont want to assign a numerical value for NA so only want to sum yes and no but skip when the choice is NA? I tried the excel way SUMIF(c1,c2.."<>NA") but didnt work for me. Just added this row to the choices sheet in your example

yesno na NA

Thanks!

Will yes and no have the same values or different?

yes=1 no=0, that way I can put a score of choices by summing the responses. Thanks

So only yes will be counted because no and NA will have 0 value right?

Yes is counted as 1 and no as 0 but NAs should not be considered as 0. I just want to skip NAs from the calculation. Makes sense? Thank you as usual!

If I want to know how many responses were NAs in a group of questions, then I guess it will be a problem if I count them as 0s

I think i need to know a bit more about what you want to achieve to help you because I feel lost.
Could you provide a sample case step by step? You said:

Yes is counted as 1 and no as 0 but NAs should not be considered as 0.

so how do you want to count NAs or you don't want to count them at all?

Hi,
So what I want to achieve is to calculate score at the end of each group of questions based on whether they responded yes or no for each questions. My problem is there may be few scenarios where yes or no cannot be an option so NA should be selected in that case. At the end, I would like to calculate percent 'yes' or 'No' out of those who responded either choice but I don't want to count NA into the denominator. Basically I want to leave out NA from calculation but want to keep it as a choice option with a followup comment. Simply put, my question is how can I add only Yes and No while omitting NA from the calculation. THANKS!

So would solve your problem like:
calcFruits2.xlsx (25.5 KB)

Hi,

Please help me , we want to calculate the sum of all three field and check the total value of sum is equal to one field.
total.xlsx (10.1 KB)

What you need is to add a constraint in your form which forces the user to put valid number. see the attached xlsform.

total_constraint.xlsx (14.7 KB)

Similar type of problem has answer in the forum. So before post your problem requesting to find if the problem has a answer in the forum.