Hey all, I've run into (as far as I've encountered) about the most tricky bit of formula roadblocks I've had in a while.... I have essentially two spreadsheets of data, and one block of output information as shown below. The bottom spreadsheet shows part numbers, then which A-Zone and and T-Zone they go through, and how long it takes to go through each zone. The middle sheet shows the part numbers, and which zone they are in on any given day. The top one is what I want to have as an output.
I would like to write a formula that that will count up the number of units whose current days status is that of "A" but only for those that are listed as being in "A-Zone 1". So it looks at the part number, checks under todays column, see's if it's in 'A', then only counts it if it's an "A-Zone 1" part.
Secondly, instead of a count, I would want a sum. So check the part number, check the current days status, then sum up the "A-Times" only for those parts who are in "A-Zone 1".
Im trying to even just conceptually think how to go about this. I've considered using boolean sumproducts and countifs/sumifs, but I'm totally stuck on how to even start to make it happen.
This might not be exactly clear, so I'm happy to try to explain further if. Any help, even just forward progress would be rediculously appreciated!
A-Zone 1 Units A-Zone 1 Time
PN 9/19 9/20 9/21 9/22 9/23 123456 O O A A T 234567 A A T T S 345678 A A A T S 456789 A T S
A-Zone T-Zone A-Time T-Time 123456 1 1 10 15 234567 2 2 20 25 345678 3 3 30 35 456789 4 4 40 45
Bookmarks