Counting number of dates in an array

[jm22]

Hello,

I've been searching all over to find a way to do this, but I haven't been able to get anything to work. I am using Excel 2003.

Here is my setup. I have an array that has a student name on the left-most column, then a series of columns with dates which correspond to the date that student completed each event.

Name Event1 Event2 Event3 ... EventN
----------------------------------------
Name1 Date1 Date2 DateN
Name2 Date3

I can accomplish this with COUNTIF assuming that the array stays constant and I can manually input the row associated with each name. I am using =COUNTIF($A6:$AV6,"<="&$D4+(F$3*30)) to accomplish this. The conditional is just the way I have to filter each date.

The problem I'm running into is that the array itself does not stay constant. It is always changing and I need to be able to match the name from another sheet to get the row number to put in the COUNTIF formula. I am able to get the row number I need via the MATCH formula, but I can't put that in the COUNTIF cell reference.

I THINK the basic question here is how can I count cells in an array based off of a dynamic row number. Does anyone have any insight on this?

 

[Aladin Akyurek]

Something like...

=COUNTIF(INDEX($B$6:$AV$600,MATCH(Name,$A$6:$A$600,0),0),"<="&$D4+(F$3*30))

 

[jm22]

This works great. Thank you!