Formula that needs to reference multiple columns

BESP10 · 03/04/19

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 · 03/05/19

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 · 03/05/19

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 · 03/06/19

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å · 03/06/19

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 · 03/06/19

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

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

Paul Newcome · 03/06/19

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

BESP10 · 03/06/19

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 · 03/06/19

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?

Formula that needs to reference multiple columns

Comments

Help Article Resources

Categories