Conditionally sum cells in a table

[Former Community Member]

I am creating a form that will have several rows of pricing that I would like to total based on the answer to another cell in the same row.  For example, I have in each row a cell in column 2 called "scope" that is a drop down yes/no field.  At the end of that row a cell called "rowtotal" that is a numeric field with a price in it.  I want to have a total at the bottom of the page for all of the rows that have "yes" selected in the "scope" dropdown.  I would prefer to use formcalc but any help is appreciated. 

[Niall_O_Donovan · Accepted solution]

Hi,

FormCalc is easier when doing a straight Sum of a repeating row' object. However when doing a conditional sum, while both will work, I am more familar with the JavaScript syntax:

// Resolve the nodes
var oRows = xfa.resolveNodes("Row1[*]"); 
Total.rawValue = 0; 
// Loop through the instances of Row1
for (var i=0; i<oRows.length; i++) {
     if (oRows.item(i).scope.rawValue == "Yes") {
          Total.rawValue =+ oRows.item(i).rowtotal.rawValue; 
     }
}

Hope that helps,

Niall

[Former Community Member]

Aha! A loop is exactly what I needed. I was able to take your example

and write it in formcalc, which I am much more familiar with than

Javascript.

I am learning my lesson about copying forms from other programs though,

it is certainly not saving me time. I didn't think this form would need

much calculation so it would be safe to copy if from excel but even to

do a simple total at the end of the form I am having to do alot of

renaming and adjusting - it would have been much cleaner to start from

scratch!

[JNORTH123]

i know this is an old thread, but would you mind throwing in the example script of the one that worked?

thanks so much.

[Former Community Member]

It took me a few minutes to even figure out which form this question was

about, I haven't worked on these for so long.

Var yeses = 0

for count = 0 upto 120 do

if (form1.mainsubform.allofformtable.row[count].scope == 1) then

yeses = yeses + form1.mainsubform.allofformtable.row[count].subtotal

endif

endfor

$ = yeses

Here is the code I have for the field. Let me know if you need more, I

will try to remember what I did!

[Jodi1725]

HI Niall,

I need to do something like this but have a column that are checkboxes.  So that if the amount falls into a certain category they mark the check box.  How would you udpate the script for the check box value?
Thanks!

Jodi