Importing matching criteria with data and a dynamic range.

[Hard Headed]

I hope this post is not inappropriate for a newbie, if so please set me straight. I can do simple things with Excel but what I need is beyond my abilities. I've spent many hours over the last two weeks searching the net trying to find an example I could modify but I surrender. I need to import from the "Data" sheet to the "Summary" sheet information that matches 2 criteria in columns A and B with the corresponding data is in columns W,X and Z with a dynamic range of the last 20 data points entered that match the criteria. I've tried combinations of the formulas below with no luck. There will be about 10 combinations of columns A and B. If this can be done without using VBA I should be able to take one example and modify it for the other combinations. Once the last 20 data points are in the Summary sheet I need the average, standard deviation and CPK from each column but I should be able to manage that. Once you look at the attached example I hope my description will make more sense, I tried to keep it brief.

SUBTOTAL(1,Data!W:W,OFFSET(<wbr>Data!A:A,COUNTIFS(Data!A:A,"<wbr>L54L",Data!B:B,"18inch Dunlop")-21,0,20,1))
SUBTOTAL(1,(OFFSET(Data!W1,<wbr>COUNTA(Data!W:W)-21,0,20,1)))
COUNTIFS(Data!A:A,"L54L",Data!<wbr>B:B,"18inch Dunlop")

Example Link https://1drv.ms/x/s!Av8u6IVc72Wun07fH5nltAMgTpbz


Data Sheet

[/URL][/IMG]

Summary Sheet

[/URL][/IMG]

Thanks,
Ken

 

[Chrisdontm]

Try this;

https://www.dropbox.com/s/yv3fokf1uxtsjkk/Kens-Puzzle.xlsx?dl=0