Can anyone help???

[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....

 

[Robert Mika]

Hi Danielle,
For SUMPRODUCT to work all ranges must be the same lenght whereas in your case formula you need would return a range of a diffrent lenght.
Try this
(Array formula confirm CTRL+SHIFT+ENTER):
=SUMPRODUCT(--(INDEX($A$1:$A$10000,MATCH(I7,$A$1:$A$10000,0)):INDEX($A$1:$A$10000,MATCH(EndOfYear,$A$1:$A$10000,0))=$A7),(INDEX($AN$1:$AN$10000,MATCH(I7,$A$1:$A$10000,0)):INDEX($AN$1:$AN$10000,MATCH(EndOfYear,$A$1:$A$10000,0))))

This will return always range of the same lenght where I7 is the start of the range and EndOfYear is the named cell to point to the end of the range.