Array formula to extract information in-between two dates

**Kayees** · 05-25-2013, 03:02 AM

Hi duanzhuanming,

I find this SUMPRODUCT formula very unstable. It works well will little data but as soon as the data grows, it gives weird behavior.
Attached is an example where I added more data then the formula "broke". To proof the point easily I changed the "Place visited" to the persons name so that it is easy to see inconsistencies.

**duanzhuanming** · 05-25-2013, 05:00 AM

Originally Posted by Kayees

Hi duanzhuanming,

I find this SUMPRODUCT formula very unstable. It works well will little data but as soon as the data grows, it gives weird behavior.
Attached is an example where I added more data then the formula "broke". To proof the point easily I changed the "Place visited" to the persons name so that it is easy to see inconsistencies.

Hi,
The formula will return the incorrect value if it finds two true conditions! you can see the attached shot! on the same day(08/02)--> 2 different places! In this case, using "If" function instead of Sumproduct

=IFERROR(INDEX(Database!$A$11:$A$225,MIN(IF((Database!$C$11:$C$225<=B$2)*(Database!$D$11:$D$225>=B$2)*(Database!$A$11:$A$225=$A3),ROW(Database!$B$11:$B$225)-10,6553655))),"")

Confirmed with Ctrl+shift+Enter!
see attached file,