Formula that needs to reference multiple columns

BESP10

Good evening, 

We are looking of how we would structure a formula to calculate the % of our production job each series is responsible for.

• There are 6 series (sequences) in this job. 1 thru 6. In the "Series ID" column. There are 300 rows +/- that make up the 6 series
• The 6 series make up 100% of the job as calculated in the "% of Total Production Time"  column
• what formula would I use to calculate what % of the job Series 1, 2, 3 is etc ?

 

Paul Newcome

You would use a SUMIFS() formula along the lines of...

 

=SUMIFS([% of Total Production Time]:[% of Total Production Time], [Series ID]:[Series ID], #)

 

Just put the number 1 - 6 in place of the # to get a total of all percentages for that particular series.

BESP10

Thank you , I see that this will work, my issue is going to be that I am keeping all of my metrics at the top of the sheet so my data rows do n ot start until Row 85 (parent row) and Row 86 is where the children rows start

 

Is there a way to start the tabulation from row 85 and include all the children rows as they get added ?

 

Thanks

 

 

BESP10

Good afternoon

So I am trying to make it count rows 89 to 100 but something is wrong with my formula

Anyone have any insight? 

 

Thank you 

=SUMIFS([% of Total Production Time]89):[% of Total Production Time]1000), [Series ID]:[Series ID], 1)

 

Andrée Starå

Hi,

Your formula is referencing 89-1000 so change it to 100 and should be good to go.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Yes. Simply replace the Column:Column range with a CHILDREN() range.

 

=SUMIFS(CHILDREN([% of Total Production Time]85), CHILDREN([Series ID]85), #)

Paul Newcome

The additional closed parenthesis after each cell reference will cause an error as well.

BESP10

HMMM, So sorry guys,

 

It is showing me a zero, per below snap shot

 

I am stumped

 

=SUMIFS(CHILDREN([% of Total Production Time]89), CHILDREN([Series ID]89), 1)

Paul Newcome

The [% of Total Production Time] column... Is that numbers in the column and then the column formatted as a percentage or is the % being added manually?