Match Range within a Formula

[Daniellel]

How can I get excel to look for a changing range within a formula (Maybe using Match????)

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!!! What I am trying to get at is something along the lines of this....

=SUMPRODUCT(--('Holiday and Sickness Tracker 11'!(MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0): (MATCH(End Of Year,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0))=$A7),('Holiday and Sickness Tracker 11'!$AN$1:$AN$9916))

But it is not working at all???? So the range needs to be a MATCH formula (Not sure even if this is the right way to so it but its all I can think of)

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

 

[edvwvw]

I am not famailiar with Excel 200 but this works in 2003 and 2007

If you are looking for the first row that , I presume, the date appears :

Starting Row =MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916, FALSE) +1


the last row that it appears:


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

you will have to specify a date for "End of Year"

edvwvw

 

[Daniellel]

Did you mean this???

And yes i have put 'End of Year' in

=SUMPRODUCT(--(MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916, FALSE)+1)MATCH(End Of Year,'Holiday and Sickness Tracker 11'!$A$1:$A$9916, TRUE)+1),('Holiday and Sickness Tracker 11'!$AN$1:$AN$9916))

 

[edvwvw]

As long as the ranges within sumproduct are the same the function should work. You do not need MATCH for it to work.

=Sumproduct(--('Holiday and Sickness Tracker 11'!$A$1:$A$9916 = I7),('Holiday and Sickness Tracker 11'!$AN$1:$AN$9916=End date))

I will be away from my desktop until tomorrow now

edwvwv