Spreadsheet: 50362d1249753006-using-variable-array-formulae-variable-sub-array-example
Sheet: Better Method

Data Arrays start at Row: |  |  | 6 | Data Arrays end at Row: |  |  | 25 | Data Arrays No. Rows= |  |  | 20
Data Array (Date) starts at Column C: |  |  | 3 | Data Array (Date) ends at Column C: |  |  | 3 | Data Array (Date) No. Cols.= |  |  | 1
Data Array (Event) starts at Column E: |  |  | 5 | Data Array (Event) ends at Column L: |  |  | 12 | Data Array (Event) No. Cols.= |  |  | 8
DATA |  | Date |  | Events Scheduled
 | Date Used=> | 2009-08-06 00:00:00
 |  | 2009-07-31 00:00:00 |  | A |  |  |  |  |  |  |  |  | ,A,,,,,,,,
 |  | 2009-08-01 00:00:00 |  | A |  | C |  |  |  |  |  |  | ,A,,C,,,,,,
 |  | 2009-08-01 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-02 00:00:00 |  |  | C |  |  |  |  |  | A |  | ,,C,,,,,,A,
 |  | 2009-08-03 00:00:00 |  | A | B |  |  |  | F |  |  |  | ,A,B,,,,F,,,
 |  | 2009-08-04 00:00:00 |  | B |  |  |  |  |  |  |  |  | ,B,,,,,,,,
 |  | 2009-08-05 00:00:00 |  | A | B |  |  |  |  |  |  |  | ,A,B,,,,,,,
 |  | 2009-08-06 00:00:00 |  | A |  |  |  |  |  | G |  |  | ,[NAME],,
 |  | 2009-08-06 00:00:00 |  |  | B |  | D |  |  |  |  |  | ,,B,,D,,,,,
 |  | 2009-08-06 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-07 00:00:00 |  |  |  |  |  |  |  |  |  | A | ,,,,,,,,,
 |  | 2009-08-07 00:00:00 |  | A |  |  | D |  |  |  |  |  | ,A,,,D,,,,,
 |  | 2009-08-08 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-09 00:00:00 |  | A |  |  |  |  |  |  |  |  | ,A,,,,,,,,
 |  | 2009-08-09 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-10 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-11 00:00:00 |  |  |  |  | D |  |  |  |  |  | ,,,,D,,,,,
 |  | 2009-08-12 00:00:00 |  | A |  |  |  | E |  |  |  |  | ,A,,,,E,,,,
 |  | 2009-08-13 00:00:00 |  |  |  |  |  |  |  |  |  |  | ,,,,,,,,,
 |  | 2009-08-14 00:00:00 |  | A |  |  |  |  |  |  |  |  | ,A,,,,,,,,
 |  |  |  | A
OBTAINED RESULTS:
 | Event | Count | 1st Event | Last | Count | Most Recent | Occurring | Count | Next Scheduled
 | Name | Events |  | Event | Events | Event | Today? | Events | Event
 |  | [E6:L25] | [E6:L25] | [E6:L25] | before | before |  | after | after
 |  | (Total) | (Total) | (Total) | 2009-08-06 00:00:00 | 2009-08-06 00:00:00 | 2009-08-06 00:00:00 | 2009-08-06 00:00:00 | 2009-08-06 00:00:00
 | A | 10 | 2009-07-31 00:00:00 | 2009-08-14 00:00:00 | 5 | 2009-08-05 00:00:00 | yes | 4 | 2009-08-07 00:00:00
 | B | 4 | 2009-08-03 00:00:00 | 2009-08-06 00:00:00 | 3 | 2009-08-05 00:00:00 | yes | 0 | None
 | C | 2 | 2009-08-01 00:00:00 | 2009-08-02 00:00:00 | 2 | 2009-08-02 00:00:00 | no | 0 | None
 | D | 3 | 2009-08-06 00:00:00 | 2009-08-11 00:00:00 | 0 | None | yes | 2 | 2009-08-07 00:00:00
 | E | 1 | 2009-08-12 00:00:00 | 2009-08-12 00:00:00 | 0 | None | no | 1 | 2009-08-12 00:00:00
 | F | 1 | 2009-08-03 00:00:00 | 2009-08-03 00:00:00 | 1 | 2009-08-03 00:00:00 | no | 0 | None
 | G | 1 | 2009-08-06 00:00:00 | 2009-08-06 00:00:00 | 0 | None | yes | 0 | None
 | H | 0 | None | None | 0 | None | no | 0 | None
 |  | IF($B35<>"",COUNTIF($E$6:$L$25,$B35),0)
 |  |  | IF($C35,INDEX($C$6:$C$25,MATCH("*,"&$B35&",*",$N$6:$N$25,0)),"None")
 |  |  |  | IF($C35,LOOKUP(2,1/ISNUMBER(SEARCH(","&$B35&",",$N$6:$N$25)),$C$6:$C$25),"None")
 |  |  |  |  | IF($C35,COUNTIF($N$6:INDEX($N$6:$N$25,COUNTIF($C$6:$C$25,"<"&$F$32)),"*,"&$B35&",*"),0)
 |  |  |  |  |  | IF(OR($C35=0,$D35>=$G$32),"None",LOOKUP(2,1/(ISNUMBER(SEARCH(","&$B35&",",$N$6:$N$25))*($C$6:$C$25<$G$32)),$C$6:$C$25))
 |  |  |  |  |  |  | IF($C35>($F35+$I35),"yes","no")
 |  |  |  |  |  |  |  | IF($C35,COUNTIF(INDEX($N$6:$N$25,1+COUNTIF($C$6:$C$25,"<="&$J$32)):$C$25,"*,"&$B35&",*"),0)
 |  |  |  |  |  |  |  |  | IF($I35,INDEX($C$6:$C$25,MATCH(1,INDEX(ISNUMBER(SEARCH("*,"&$B35&",*",$N$6:$N$25))*($C$6:$C$25>$J$32),0),0)),"None")