SUMIFS formula

[mannyd3lr3y]

Hi there,

Can somebody please help me out with a formula?  I am creating a form using LiveCycle Designer which will require a script equivalent to the SUMIFS on Excel.  What I am trying to do is sum up all "yes" costs column which is relative to a yes/no dropdown column.  I have been trying to research this for two days now and still no luck.  I really hope someone could help me out.

Thanks in advance.

[_Bruce_Robertson]

Hi, There is nothing built in, you will have to write some script but it would not be much, the general pattern for summing a table column is to have something like this in the calculate event.

var sum = 0;
for (var i=0; i < {instanceManager}.count; i++)
sum += {field}.rawValue;
sum;

All you would need would be

var sum = 0;
for (var i=0; i < {instanceManager}.count; i++)

{

if ({costfield}.rawValue = 'Yes')

{
  sum += {field}.rawValue;

}

}
sum;

Where the things in curly brakets will be specific to your form.

Good luck

Bruce

[mannyd3lr3y]

Hi Bruce,

Thank you however it did not work under JavaScript.  Maybe I did it wrong because I am not very familiar with JavaScript; I have only used FormCalc in the past.  Or maybe my question was not clear, but what I am trying to accomplish is to write a script similar to the following excel formula:

=SUMIFS(A2:A4,B2:B4,"Yes")

should the table look like this:

Header 1	Column A	Column B
Row 1	10	Yes
Row 2	5	Yes
Row 3	10	No

I found "if" scripts online, however I am not sure who to sum it up.

Again, thank you.

[mannyd3lr3y]

	A	B
2	10	Yes
3	5	Yes
4	10	No

Sorry for the confusion on the table.  I am actually trying to copy an excel spreadsheet

[_Bruce_Robertson]

Hi,

If we assume your table row is called itemRow and there is a field called costItem (which can be Yes or No ... should this be a text feild) and a field called amount (that you want to sum up), I would use the following code in the calculate event of the field displaying the result.

var sum = 0;
for (var i=0; i<_itemRow.count; i++)
{
if (itemRow.all.item(i).costItem.rawValue == "Yes")
{
  sum += itemRow.all.item(i).amount.rawValue;
}
}
sum;

Good luck

Bruce

[mannyd3lr3y]

How do I capture the whole row?  In Excel, I can do a range; How do I do that with javascript?

[_Bruce_Robertson]

Hi,

So in your example you want to sum up both columns?

In that case I would change the line " sum += itemRow.all.item(i).amount.rawValue;"

to

sum += itemRow.all.item(i).amount.rawValue + itemRow.all.item(i).othercolumn.rawValue;

You will have to change "othercolumn" to suit the name of your column.

Regards

Bruce

[mannyd3lr3y]

Hi Bruce,

I am so sorry for being such a pain but let me show you how my table looks like and maybe it will help me explain what I am trying to accomplish here.

	Pre-paid	Amount
Air
Bus
Rail
Rent-A-Car
Parking
Gas
Shuttle
Taxi
Personal Car
Registration Fee
Hotel
Internet Fee
Meals
Tolls
Phone Calls
Per Diem
Misc
Total Expenses Incurred
Pre-paid Expenses
Amount due to Claimant

The  "Pre-paid" column is a drop-down with options of Yes, No, and N/A in which I renamed each cell to "PrePaid".   What I would like to do is to sum-up the amount on the "Amount" column  (which all the cells I have renamed to "Amount") relative to the "Yes" on the "Pre-paid" column on the "Pre-paid Expenses" box.

Just a note again, all cells below the "Pre-paid" column, I renamed "PrePaid".  All the cells below the "Amount" column, I renamed "Amount".  Was I supposed to do that? Are all the cells supposed to be JavaScript.  "Total Expenses Incurred" is FormCalc since I just added all the way down.  "Amount due to Claimant" is FormCalc as well since it is just the difference of "Total Expenses Incurred" and "Pre-paid Expenses".

http://forums.adobe.com/thread/426018 I found this thread and it is very similar to what I am trying to accomplish, however when I plugged in the code, it did not work; I believe because I already have a pre-made table, whereas for that particular instance, you have an option of adding a row.

I really appreciate your help and again, thank you!

[_Bruce_Robertson]

Hi, Sorry I'm obviously missing something here.  Can you host the form somewhere (e.g. Acrobat.com) and reply with the link and I'll have a look.

[mannyd3lr3y]

https://acrobat.com/#d=xsZJcrrFXHfNE4fDF6PviA

Thanks

[_Bruce_Robertson]

Can you set the file to shared, I get a "you do not have access' message.

[mannyd3lr3y]

https://acrobat.com/#d=xsZJcrrFXHfNE4fDF6PviA

here you go... I published it.

Thank you!

[_Bruce_Robertson]

Hi, I hope I understand it now. try this in the calculate event

var result = 0; // initialise calculation
var rows = xfa.resolveNodes("Table1.*.(className=='subform')"); // select all row subforms (e.g. Row1, Row2, ...)
for (var i=0; i < rows.length; i++)
{
var item = rows.item(i);
if (item.PrePaid)  // If the row has a PrePaid field
{
  if (item.PrePaid.rawValue == "No") // If the expense is not PrePaid
  {
   result += item.Amount.rawValue;  // Add the amount to the result
  }
}
}
result;

Regards

Bruce

[mannyd3lr3y]

Was I supposed to rename each row? If so, do I leave this part "select all row subforms (e.g. Row1, Row2, ...)" as is or should I change it to whatever the cell name is?

[_Bruce_Robertson]

No, just paste it into the calculate event of form1.#subform[0].Table1.Row22.SUMExpenses and change the langauge to JavaScript.  Here's a link to a version I used to test the script.  https://acrobat.com/#d=CZiwMcAdnAfgicYa-oTD8g

You could have renamed the rows, it would have made the script a little easier, so the line

     var rows = xfa.resolveNodes("Table1.*.(className=='subform')");

could have been something like

     var rows = xfa.resolveNodes("Table1.Row[*]");

or maybe even a prue JavaScript version;

     for (var i=1; i<Table1.Row1.nodes.length; i++)
     {
           result += Table1.Row1.nodes.item(i).rawValue
     }

And if you only renamed the rows with the cells PrePaid and Amount you would not need the line "if (item.PrePaid)" which was only there to filter out the header and subtotal rows.

Though, I tend not to battle with the way Designer generates fixed size tables ... maybe it depends on how many forms you have to do.

Bruce

[mannyd3lr3y]

THANK YOU SOOOOO MUCH, BRUCE!!!