Spreadsheet: 50362d1249753006-using-variable-array-formulae-variable-sub-array-example
Sheet: Data and Results

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
 |  | 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? |  | Event
 |  | [E6:L25] | [E6:L25] | [E6:L25] | before | before |  | after | after
 |  | (Total) | (Total) | (Total) | 2009-08-08 00:00:00 | 2009-08-08 00:00:00 | 2009-08-08 00:00:00 | 2009-08-08 00:00:00 | 2009-08-08 00:00:00
 | A | 10 | 2009-07-31 00:00:00 | 2009-08-14 00:00:00 | 7 | 2009-08-07 00:00:00 | no | 3 | 2009-08-09 00:00:00
 | B | 4 | 2009-08-03 00:00:00 | 2009-08-06 00:00:00 | 4 | 2009-08-06 00:00:00 | no | 0 | #N/A |  |  | The problem is solved except for a tiny detail:
 | C | 2 | 2009-08-01 00:00:00 | 2009-08-02 00:00:00 | 2 | 2009-08-02 00:00:00 | no | 0 | #N/A |  |  | The only way I can see to get rid of the #[NAME]
 | D | 3 | 2009-08-06 00:00:00 | 2009-08-11 00:00:00 | 2 | 2009-08-07 00:00:00 | no | 1 | 2009-08-11 00:00:00 |  |  | errors is using Conditional Formatting -
 | E | 1 | 2009-08-12 00:00:00 | 2009-08-12 00:00:00 | 0 | #N/A | no | 1 | 2009-08-12 00:00:00 |  |  | i.e. set font colour to the same as the background
 | F | 1 | 2009-08-03 00:00:00 | 2009-08-03 00:00:00 | 1 | 2009-08-03 00:00:00 | no | 0 | #N/A |  |  | (Surrounding with an IF statement fails, as does
 | G | 1 | 2009-08-06 00:00:00 | 2009-08-06 00:00:00 | 1 | 2009-08-06 00:00:00 | no | 0 | #N/A |  |  | surrounding with IFERROR)
 | H | 0 | None | None | 0 | #N/A | no | 0 | #N/A
COMMENTS
Cell C35,copied down, is: IF($B35<>"",COUNTIF($E$6:$L$25,$B35),0)
Cell D35, copied down, is: IF($C35>0,INDEX($C$6:$C$25,SMALL(IF($E$6:$L$25=$B35,ROW($C$6:$L$25)),1)-($D$1-1),1),"None")
Cell E35, copied down, is: IF($C35>0,INDEX($C$6:$C$25,LARGE(IF($E$6:$L$25=$B35,ROW($C$6:$L$25)),1)-($D$1-1),1),"None")
Cell F35, copied down, is: IF($B35<>"",COUNTIF(OFFSET($E$6,0,0,SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1),$L$3),$B35),"")
Cell G35, copied down, is: INDEX($C$6:$C$25,LARGE(IF(OFFSET($E$6,0,0,SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1),$L$3)=$B35,ROW(OFFSET($E$6,0,0,SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1),$L$3))),1)-($D$1-1),1)
Cell H35, copied down, is: IF($C35>($F35+$I35),"yes","no")
Cell I35, copied down, is: IF($B35<>"",COUNTIF(OFFSET($E$6,LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1),0,$L$1-LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1),$L$3),$B35),"")
Cell J35, copied down, is: INDEX($C$6:$C$25,SMALL(IF(OFFSET($E$6,LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1),0,$L$1-LARGE(IF($C$6:$C$25=[NAME]=TODAY(),ROW($C$6:$C$25)+1-$D$1),1),0,$L$1-LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1),$L$3))),1)-($D$1-1),1)
 | (F35),(G35) : depth of 1st sub-array (before today)
 | SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1)
 | 12
 | (I35),(J35) : depth of 2nd sub-array (after today)
 | $L$1-LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1)
 | 7
 | (G35) : largest row number within 1st sub-array (before today) which contains the contents of $[NAME]
 | LARGE(IF(OFFSET($E$6,0,0,SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1),$L$3)=$B35,ROW(OFFSET($E$6,0,0,SMALL(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)-$D$1),1),$L$3))),1)-($D$1-1)
 | 12
 | (I35),(J35) : offset from $C$6 to start of 2nd sub-array (after today)
 | LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1)
 | 13
 | (I35),(J35) : absolute row number to start of 2nd sub-array (after today)
 | ROW(INDEX($C$6:$$25,LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1+1),1),1))
 | 19
 | (I35),(J35) : depth after today
 | $L$1-LARGE(IF($C$6:$C$25=TODAY(),ROW($C$6:$C$25)+1-$D$1),1)
 | 7