summation based on criteria

New Here ,
Mar 21, 2019

Copy link to clipboard

Copied

Hi Guys,

I have a question. I am trying to write a script with no success that sums up different cells based on their Job titles as shown in the picture below.

I Need the Hour(s) column highlighted in red to get its value from the sum in column highlighted in blue based on the job number.

example: all the "Apples" job number to be summed and displayed in the Hour(s) column highlights in red.

in excel it would be something like this. (JobNumber and Totaltime would be the cell range)

Apple Hours = SUMIF(JobNumber[0],"Apples",TotalTime(Hours)[0])

Sumif(Range, Criteria, [sum_range])

Most Valuable Participant
Correct answer by try67 | Most Valuable Participant

I recommend you give the fields consistent names. It will make writing the script much easier.

Let's say the job fields start from "Job Number 1.0" and go to "Job Number 1.7", just like the Total Hours fields.

In that case you would be able to use this code as a doc-level script:

function calcJobHours(jobNumber) {

    var total = 0;

    for (var i=0; i<=7; i++) {

        if (this.getField("Job Number 1."+i).valueAsString==jobNumber)

            total+=Number(this.getField("Total Time 1."+i).valueAsString);

    }

    event.value = total;

}

You can then call it from the custom calculation event of the total hours per job fields, like this:

calcJobHours("A101");

Or:

calcJobHours("A105");

etc.

TOPICS
Acrobat SDK and JavaScript, Macintosh, Windows

Views

193

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

summation based on criteria

New Here ,
Mar 21, 2019

Copy link to clipboard

Copied

Hi Guys,

I have a question. I am trying to write a script with no success that sums up different cells based on their Job titles as shown in the picture below.

I Need the Hour(s) column highlighted in red to get its value from the sum in column highlighted in blue based on the job number.

example: all the "Apples" job number to be summed and displayed in the Hour(s) column highlights in red.

in excel it would be something like this. (JobNumber and Totaltime would be the cell range)

Apple Hours = SUMIF(JobNumber[0],"Apples",TotalTime(Hours)[0])

Sumif(Range, Criteria, [sum_range])

Most Valuable Participant
Correct answer by try67 | Most Valuable Participant

I recommend you give the fields consistent names. It will make writing the script much easier.

Let's say the job fields start from "Job Number 1.0" and go to "Job Number 1.7", just like the Total Hours fields.

In that case you would be able to use this code as a doc-level script:

function calcJobHours(jobNumber) {

    var total = 0;

    for (var i=0; i<=7; i++) {

        if (this.getField("Job Number 1."+i).valueAsString==jobNumber)

            total+=Number(this.getField("Total Time 1."+i).valueAsString);

    }

    event.value = total;

}

You can then call it from the custom calculation event of the total hours per job fields, like this:

calcJobHours("A101");

Or:

calcJobHours("A105");

etc.

TOPICS
Acrobat SDK and JavaScript, Macintosh, Windows

Views

194

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 21, 2019 0
Most Valuable Participant ,
Mar 21, 2019

Copy link to clipboard

Copied

What are the names of the Total Time and Job Number fields?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
New Here ,
Mar 21, 2019

Copy link to clipboard

Copied

I have created a table to give a better representation. I hope this clarifies a bit more.

TABLE 1

DateHoursJob Number
Monday10A101
Tuesday5A105
Wednesday30A111
Thursday3A101
Friday25A111
Saturday23

A105

TABLE 2

Job NumberTotal Hours Done on the Job
A101= SUM hours from "Hours" column if Job Number in Column "Job Number" contains A101 (Monday Hours + Thursday Hours gives a total of 13 hours)
A105= SUM hours from "Hours" column if Job Number in Column "Job Number" contains A105 (Tuesday Hours + Saturday Hours gives a total of 28 hours)
A111= SUM hours from "Hours" column if Job Number in Column "Job Number" contains A111 (Weds Hours + Friday Hours gives a total of 55 hours)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
Most Valuable Participant ,
Mar 21, 2019

Copy link to clipboard

Copied

I understood the request, but in order to help you with the code for it I need to know the names of the fields...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
New Here ,
Mar 21, 2019

Copy link to clipboard

Copied

Job Fields:

"Job Number 1.1"

"Job Number 1.2"

"Job Number 1.3"

"Job Number 1.4"

"Job Number 1.5"

"Job Number 1.6"

"Job Number 1.7"

"Job Number 1.8"

Total Hours Fields:

"Total Time 1.0"

"Total Time 1.1"

"Total Time 1.2"

"Total Time 1.3"

"Total Time 1.4"

"Total Time 1.5"

"Total Time 1.6"

"Total Time 1.7"

Total hours done on the job Fields:

"A101"

"A105"

"A111"

This what your asking for? I'm not the greatest with Javascript, it's a learning process for me.

(Worse case you can make up names of the fields for the script and I can break it down and insert the changes I need to make it work.)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
Most Valuable Participant ,
Mar 21, 2019

Copy link to clipboard

Copied

I recommend you give the fields consistent names. It will make writing the script much easier.

Let's say the job fields start from "Job Number 1.0" and go to "Job Number 1.7", just like the Total Hours fields.

In that case you would be able to use this code as a doc-level script:

function calcJobHours(jobNumber) {

    var total = 0;

    for (var i=0; i<=7; i++) {

        if (this.getField("Job Number 1."+i).valueAsString==jobNumber)

            total+=Number(this.getField("Total Time 1."+i).valueAsString);

    }

    event.value = total;

}

You can then call it from the custom calculation event of the total hours per job fields, like this:

calcJobHours("A101");

Or:

calcJobHours("A105");

etc.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
New Here ,
Mar 21, 2019

Copy link to clipboard

Copied

Hi,

Thanks, but when I insert this coding into acrobat it says there's an error.

SyntaxError: Missing formal parameter 1: at line 2

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 21, 2019 0
Most Valuable Participant ,
Mar 22, 2019

Copy link to clipboard

Copied

I'm not getting this error. Did you change something in the code?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 22, 2019 0
New Here ,
Mar 22, 2019

Copy link to clipboard

Copied

I inserted the coding wrong. It works perfectly, I can't thank you enough.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 22, 2019 0