Equation help

Sean Murphy

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

Paul Newcome

Try this...

=SUMIFS([Task SF in Progress]:[Task SF in Progress], Task:Task, "Fr. Sh.", Status:Status, OR(@cell = "In Progress", @cell = "Completed"))

Paul Newcome

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.

Paul Newcome

Try this...

=SUMIFS([Task SF in Progress]:[Task SF in Progress], Task:Task, "Fr. Sh.", Status:Status, OR(@cell = "In Progress", @cell = "Completed"))

Sean Murphy

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?

Sean Murphy

Actually, disregard, it worked in a different column. This is saving me a ton of time, thanks again!!

Paul Newcome

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.

Paul Newcome

Happy to help! 👍️

Sean Murphy

@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?

Paul Newcome

It does make sense. When you say a cell is "automatically filled", how is it being filled? Formula, cell link, other?

Sean Murphy

Formula- the last screen shot on the comment.

Paul Newcome

Ok. And how is [Panel SF]18 filled out?

Sean Murphy

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

Paul Newcome

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.

Sean Murphy

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

Sean Murphy

for example line 28 is not recognized. Sorry for my chopped up responses, still getting used to this world.

Paul Newcome

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.

Sean Murphy

OH I see now. 😅Thanks!!