Need help with a formula for that sums up the amount of material used based on Job numbers
I am trying to track the amount of cans we use for each work order. Each work order has a specific number and each pallet of cans loaded has a different pallet number, our operators enter in how many layers we use from each pallet. we run thousands of work orders a year with no reoccuring work orders. How can I get the total number of layers used for each work order?
Best Answer

SUMIFS will do that for you. You can create one formula that will sum all the rows that meet the IF criteria.
If your sheet looks like this:
You can use a formula like this:
=SUMIFS([Layers Used]:[Layers Used], [Job #]:[Job #], [Job #]@row)
This sums the Layers Used column where the Job # matches the Job # on the current row.
(if you want this on another sheet you can replace the column references with cross sheet references)
This will give you:
If you don't want to enter the job number in your summary table you can do the sum next to the original data instead. The formula is the same:
Answers

Have a look at SUMIFS
https://help.smartsheet.com/function/sumifs
It will sum a column (such as layers) based on the value in other columns (such as work order, date, etc.)

We area 24hr operation that runs multiple work orders a day, every batch of materials loaded needs to be specified for each order/job number
for traceabillity. I want to get the totals for each job number with out having to enter a formula everytime there is a new job number

SUMIFS will do that for you. You can create one formula that will sum all the rows that meet the IF criteria.
If your sheet looks like this:
You can use a formula like this:
=SUMIFS([Layers Used]:[Layers Used], [Job #]:[Job #], [Job #]@row)
This sums the Layers Used column where the Job # matches the Job # on the current row.
(if you want this on another sheet you can replace the column references with cross sheet references)
This will give you:
If you don't want to enter the job number in your summary table you can do the sum next to the original data instead. The formula is the same:

Thank you so much!!

Happy to help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 304 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!