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 re-occuring 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!