# Formula that needs to reference multiple columns

Options
✭✭✭✭✭✭
edited 12/09/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 ?

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!