Equation help
Hi all, I'm not very privy with formulas, but am attempting to learn. I'm working on keeping track of square footage production based on "in progress" or "completed" in a manufacturing setting. There are 6 different steps to each unit produced (on different lines, see screen shot), and I'm trying to figure out a way to calculate the totals of each specific step. I'm assuming SUMIFS would be the correct route, but any help would be useful. In the past I would individually pick each cell for a =SUM(, but I've learned there's a limit of 200 or so.
I'm hoping to avoid having to do this again
Best Answers
-
Try this...
=SUMIFS([Task SF in Progress]:[Task SF in Progress], Task:Task, "Fr. Sh.", Status:Status, OR(@cell = "In Progress", @cell = "Completed"))
-
That goes back to the Completed vs Complete. Your formula is looking for "Completed", but the Status in row 28 is "Complete". Which means it is probably also not picking up row 20 either.
Answers
-
Try this...
=SUMIFS([Task SF in Progress]:[Task SF in Progress], Task:Task, "Fr. Sh.", Status:Status, OR(@cell = "In Progress", @cell = "Completed"))
-
that worked! Thanks!
I tried to use the same formula to change it to a different task "W.P." then it threw off the first one. Can you have multiple SUMIFS in the same column?
-
Actually, disregard, it worked in a different column. This is saving me a ton of time, thanks again!!
-
It is because you are putting the formulas within one of the ranges being evaluated. You may need to update the ranges with row references to exclude the rows containing your formulas.
-
Happy to help! 👍️
-
@Paul Newcome I used this equation on my sheet, and it works great, but I noticed that if a cell automatically is filled, the formula doesn't read it, but if a number is manually changed, the equation picks up the information. Does that make sense?
-
It does make sense. When you say a cell is "automatically filled", how is it being filled? Formula, cell link, other?
-
Formula- the last screen shot on the comment.
-
Ok. And how is [Panel SF]18 filled out?
-
Manually when I set the page up.
What's interesting is the formula you made did pick up the auto-fill for the WP Totals "in progress" column
So I suspect I did something wrong when I attempted to replicate it
-
Glad you got it working. You may have run into a pretty classic issue that EVERYONE runs into eventually when doing these kinds of metrics...
Completed vs Complete
Otherwise it looks like you're pretty well set and on your way.
-
I didn't get it quite working actually, I didn't do a good job of explaining my thoughts. I'm still not having luck with having the auto-fill getting picked up by the formula, it only works if it's a manual input
-
for example line 28 is not recognized. Sorry for my chopped up responses, still getting used to this world.
-
That goes back to the Completed vs Complete. Your formula is looking for "Completed", but the Status in row 28 is "Complete". Which means it is probably also not picking up row 20 either.
-
OH I see now. 😅Thanks!!
Help Article Resources
Categories
Check out the Formula Handbook template!
