Forum Discussion
formula help
i have a big spread sheet that has a index and match formulas in it. it is looking for the date and shift the filling out information. i have all the data go to different areas with their own formal in each but what i cant figure out is there are some time when the date and shift will be the same but for different production lines and the formula only pulls the info from the first date line that matches and stops there. even though the data is in different lines it doesn't pull it. what am i missing in the formula or need to change?
thank you
7 Replies
here is the form i am creating. it is on the pass down tab. cell B12-E15, H12-K15, B18-E21, H18-K21, the cells that are of OEE are a different formulas. but I am tryin to have it look for data by date and shift.
k_musgrove I'm not going to update all your formulas but I think what you really want is SUMIFS(). Here is an example for cell B12:
=SUMIFS('Filler- Retort'!X:X,'Filler- Retort'!$A:$A,'Pass Down'!$D$1,'Filler- Retort'!$B:$B,'Pass Down'!$D$2)and this will still only pull by the date and shift? since there can be 2 shifts in one day? mtarler
- Please Share some sample data with us and the formula you have tried so far,, will help us to fix the issue !!
k_musgrove What is missing is a sample sheet for us to better understand what you are doing, how things are set up, and the such. I will take a stab at it however. So you are doing a match on some date column but 2 different "lines" (as in excel rows or as in manufacturing lines?) have the same date. It is always best to have a unique ID to use in the match. If you don't have one, and can't add one, then consider making one by combining columns.. For example:
Date Last Name First Name Deposit
1/1 Smith Joe $100
1/15 Jones Jill $50
1/1 Jones Indy $200
MATCH("1/1 Jones Indy",TEXT(A:A,"MM/DD")&" "&B:B&" "&C:C,0)
So the above match statement will only match the 3rd line
