# Importing matching criteria with data and a dynamic range.

##### New Member
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")

Data Sheet
[/URL][/IMG]

Summary Sheet
[/URL][/IMG]

Thanks,
Ken

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Replies
3
Views
277
Replies
23
Views
514
Replies
0
Views
138
Replies
3
Views
93
Replies
4
Views
592

### Forum statistics

1,190,736
Messages
5,982,654
Members
439,789
Latest member
Justhavinaltlfun2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back