Create an Excel Formula

Copper Contributor

I have a master excel file that pulls data from an online based source to track work in progress steps of specific jobs. That file uses a 5 day format to show this progress. For example, on Wednesday 2/7 five pieces show to be in heat treat. On the morning of 2/8, the system would update and generate in that excel file that now those five pieces are in inspection. So, you would see on the master file a 5 in the Wednesday column in the row labeled heat treat and on 2/8 the 5 would be in the Thursday column in the inspection row. Picture inserted for reference.Capture.PNG


What I'm trying to do is create a formula for a separate excel file that has the jobs separated by the person who over sees them. I don't need all the work in progress in formation in this file. I only need the last few steps. What I can't figure out is how to calculate these final steps into the new file. I can formulate it where I see what is a finished product on Monday but if more products are finished over night I still only see what was done on Monday. I don't need to add these values because if 5 were done on Monday and nothing was finished over night then the value should still read 5 and not 10.


I need a formula to pull a value from another file but I need cell where the value is located to change depending on the day 

1 Reply
COUNTIFS and or SUMIFS may be the formulas you are looking for. In a cell for each product manager's name, you need the column range for the names, and "name1" as the criteria. COUNTIF or SUMIF may suffice if just one range and name is needed. SUMIF, is a bit more complicated than COUNTIF, as the range to be added has to be detailed first I think

For COUNTIFS and SUMIFS you can include a lower and higher date to trap products to analyse, say if only for this month, then ">31/01/2024" and "<01/03/2024" would have to be added to the range entered twice with either having the date range examples .

You could do an average by dividing the sums by the COUNTIFS for each manager.