Trying to convert a SUMIF formula from Excel to Acrobat DC form (javascript)

[akunce]

Hi,

I have an Excel spreadsheet with a few formulas in it.

One of them is a SUMIF, and I cannot figure out how to convert it into javascript (I'm not very familiar with jv at all) for an Acrobat form.

In one column there is a drop down for clients, ABC, DEF, LMO, etc. They can choose the same client in multiple rows. In the next column is the amount spent by each client.

There is a cell (field) where it totals the amount ABC spent.

So if we have

ABC     $1,000

DEF     $1,000

ABC     $500

LMO     $150

etc.

the field for ABC total would generate $1,500, for DEF $1,000 and so on.

There are a total of six clients on the drop down list and nine fields where $ entries can be made.

The formula in Excel is =SUMIF($F$17:$F$29,$F31,G$17:G$29)

But I'll be darned if I know how to get the same result in Acrobat.

Can anyone help?

Thank you

[try67 · Accepted solution]

Let's say the fields in the first column are named Category1, Category2, etc., up to 10, and the second column fields are named Amount1, Amount2, etc.

To calculate the total amount for the ABC fields, you can use this custom calculation script:

var total = 0;
var maxFields = 10;
for (var i=1; i<=maxFields; i++) {
    if (this.getField("Category"+i).value=="ABC") 
          total+=Number(this.getField("Amount"+i).value); 
}
event.value = total;

[akunce]

It's close, but I can't get the variable to work (+i)

It will sort of work if I remove the +i and just put the full name of the field in (Amount1) but then the calculated field shows the amount in multiples of 10 (If I enter $1 the total field shows $10, and I've tried different number formats to no avail.)

It is so close, it's frustrating.

[gkaiseril]

Please post a link to a sample of a form with your problem.

[try67]

Strange... Are there any error messages in the JS Console (Ctrl+J / Cmd+J) when you run it?

[akunce]

There are no error messages when I run it with the exact name of the field.

I'm working on a copy I can link to (need to strip some information first since this is for my work.)

[akunce]

Here is the link.

I hope the linking works.

[try67]

There are errors in the console when you change the value of one of the fields... The reason it's not working is you have only 9 sets of fields, not 10, as you've specified.

[try67]

PS. You also have errors in the calculation scripts for other fields...  There are multiple instances of this error message whenever I change a value:

TypeError: f is null

That means that you used an incorrect field name in one of the first two options under the Calculate tab, either by typing it incorrectly (under the second option) or by selecting a field and then renaming/deleting it (under the first option).

[akunce]

Ah, ok.

I changed the names of many of the fields to conform more to the script you made, I must have missed some.

Changing the 10 to 9 worked!!

Thank you so much!

I really appreciate it.

[try67]

By the way, since you need to do pretty much the same thing for multiple fields I recommend using a function that takes the source value as a parameter. Put this code in a doc-level script:

function calcTotal(name) {
    var total = 0; 
    var maxFields = 9; 
    for (var i=1; i<=maxFields; i++) { 
        if (this.getField("Source"+i).value==name) 
              total+=Number(this.getField("FY1Row"+i).value); 
    } 
    event.value = total; 
}

And then you can simply call it from the calculation script like this:

calcTotal("ARD");

Or:

calcTotal("DEPT");

etc.

[try67]

This code can also be adjusted so that the column number is also a parameter, and then you could use it for the entire Totals section, with just one function...

[akunce]

Where would I put the column number in the script?

(Man, I gotta take some classes in scripting.)

You have been extremely patient with me, by the way. I really appreciate it!

[try67]

Change the main code to:

function calcTotal(name, col) { 
    var total = 0; 
    var maxFields = 9; 
    for (var i=1; i<=maxFields; i++) { 
        if (this.getField("Source"+i).value==name) 
              total+=Number(this.getField("FY"+col+"Row"+i).value); 
    } 
    event.value = total; 
} 

And then the call to it to:

calcTotal("ARD", "1");

calcTotal("ARD", "2");

calcTotal("DEPT", "3");

etc.

[try67]

Actually, if you want to use it for the Total column as well it's better to do it like this:

function calcTotal(name, colName) { 
    var total = 0; 
    var maxFields = 9; 
    for (var i=1; i<=maxFields; i++) { 
        if (this.getField("Source"+i).value==name) 
              total+=Number(this.getField(colName+"Row"+i).value); 
    } 
    event.value = total; 
} 

And then:

calcTotal("ARD", "FY1");

calcTotal("ARD", "FY2");

calcTotal("DEPT", "FY3")

calcTotal("VC", "Total")

etc.

[akunce]

Ok, so jumping around one's cubicle saying "IT WORKED!" can get some strange looks from the rest of the office.

Thank you SO much!

[SCCmcollins103]

Hi,

I also want to ask something about this sum ifs

Code    Hours                         Summary

BRV     5                                 Code         Hours

CTU    2                                   BRV        9

BRV     4                                   CTU       2

Total   11                                   Total        11

the left is the absent code that employee use and the left is the summary that total I have 31 rows.

Is this possible to make a script. it was working well in my excel but we need a pdf fillable.

I appreaciate any help..

Thankyou