Array- Pulling rows of data based on date range -- Please Help!

[MollyV]

Good afternoon,

I have been using some array formulas to pull data from multiple books. This particular formula seemed to be working but is actually just pulling the rows in order opposed to based off the date range specified. I'm not sure what I'm missing and help would be greatly appreciated.
The formula I am currently using (placed in B2):
=IF(ROWS(B$2:B2)>$Q$1,"",INDEX(Overview!B2:B16,SMALL(IF((Overview!$B$2:$B$16>=$O$1)*(Overview!$B$2:$B$16<=$P$1),ROW(Overview!B2:B16)-ROW(Overview!B2:B16)+1),ROWS(B$2:B2))))

In Cell Q1:
=SUMPRODUCT((Overview!$B$2:$B$25>=$O$1)*(Overview!$B$2:$B$25<=$P$1))

Cells O1 and P1 contain the date range

Any and all help is greatly appreciated

 

[Eric W]

Welcome to the Board!

I haven't actually tested this, but I believe your formula should be:

=IF(ROWS(B$2:B2)>$Q$1,"",INDEX(Overview!B$2:B$16,SMALL(IF((Overview!$B$2:$B$16>=$O$1)*(Overview!$B$2:$B$16<=$P$1),ROW(Overview!B$2:B$16)-ROW(Overview!B$2)+1),ROWS(B$2:B2))))

Changes marked in red.

 

[MollyV]

Thank you for your reply,
That did stop it from just pulling the next row opposed to the date criteria. However; now Its just pulling the first row and duplicating it. I have tried several different variances of the formula but they all do the same. For example I have 4 rows of data with March dates, the formula pulls the first date and shows it 4 times. I have gone through several youtube videos that show its use and working properly, double checked the formula, I am at a loss.


=IF(ROWS(B$2:B2)>$Q$1,"",INDEX(Overview!B$2:B$25,SMALL(IF((Overview!$B$2:$B$25>=$O$1)*(Overview!$B$2:$B$25<=$P$1),ROW(Overview!$B$2:$B$25)-ROW(Overview!$B$2:$B$25)+1),ROWS(B$2:B2))))

 

[Eric W]

Thank you for your reply,
That did stop it from just pulling the next row opposed to the date criteria. However; now Its just pulling the first row and duplicating it. I have tried several different variances of the formula but they all do the same. For example I have 4 rows of data with March dates, the formula pulls the first date and shows it 4 times. I have gone through several youtube videos that show its use and working properly, double checked the formula, I am at a loss.


=IF(ROWS(B$2:B2)>$Q$1,"",INDEX(Overview!B$2:B$25,SMALL(IF((Overview!$B$2:$B$25>=$O$1)*(Overview!$B$2:$B$25<=$P$1),ROW(Overview!$B$2:$B$25)-ROW(Overview!$B$2:$B$25)+1),ROWS(B$2:B2))))

Remove the part in red.