changing range within a formula???

[Daniellel]

How can i get excel to look for a changing range within a formula???

This is the current formula i am using... The bit i need to change is ****** ...
=SUMPRODUCT(--('Holiday and Sickness Tracker 11'!*********=$A7),('Holiday and Sickness Tracker 11'!$AN$1:$AN$8977))

The formula i would use to find the start and end row numbers is a Match...

Starting Row =MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)
Ending Row =MATCH(End Of Year,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)

How can i get this to work all in one sum of am i approaching it all wrong???

PLEASE HELP!!!

I am currently using Excel 2000 if that helps....

 

[Daniellel]

YOU ARE AWESOME AND AMAZING!!!!!!!!!!!!!

Thank you so so so much for your help with this, I have been trying to solve this problem for about 3 days solid!!!!


Thank you again!!!

X

 

[Jonmo1]

Glad to help, thanks for the feedback.