Formula Help with Multiple Criteria and two data sets

[Mds83]

Good morning. I'd be very grateful to anyone who could help with this. I have two data sets (see attachment).

In Column Q (Total Time) of the green set I'm trying to write a formula that will tell me the total time it takes to get from Step ___ to Step ____ represented by the data in Columns O & P.

The data that needs to be added up to get the Total Time is located in the white data set below. I need to be able to match Column B (Part_WO) in the green set to Column A in the white set so that I'm able to get the data from the correct row.

I then need look at ROW 2 (header row on white set of data) and determine which headers are >= Column O (ROUTE_STEP_NUM) AND <= Column P (NEXT_MONARCH_STEP). Then I need to take the data beneath those headers and SUM them.

Any ideas / solutions would be greatly appreciated.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Kaper]

First I thought that the "natural solution for this would be INDEX and two MATCH instructions for finding the row with Part_WO and column with NEXT_MONARCH_STEP. And next one the same but with ROUTE_STEP_NUM but then I noticed that you will need to summ all the values in between the found steps. So probably a more complicated solution would be needed. As already suggested - to help you with that, wold be nice to have a sample spreadsheet not the image.

[Kaper]

PS. I'm writing it as separete post, not editing previous one, to make it better visible.

Sample solutions in sample file:

Please Login or Register  to view this content.

or (some may find it easier to read, but it contains volatile function wtich could - if copied in many cells - slow down a spreadsheet)

Please Login or Register  to view this content.

[AliGW]

Here's another option (based on Kaper's sample workbook):

=LET(f,FILTER(Sheet2!B3:N5,Sheet2!A3:A5=B2),SUMPRODUCT(f*(Sheet2!B2:N2>=O2)*(Sheet2!B2:N2<=P2)))

[Kaper]

Ali, I don't think this formula will work for OP, because she/he declares that her/his version of Excel is 2016 (joined recently so probably it's valid information)

[AliGW]

Mmm. Didn't notice that! Never mind - it's there for anyone else to use.