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

 

[texasalynn]

the range areas must be the same size in sumproduct
$AN$1:$AN$8977
$A$1:$A$9916

so make them both be $9916

 

[Daniellel]

Thank you so much for replying, I dont think I explained what I need well enough...

What I am trying to get at issomething 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)

 

[Jonmo1]

What type of data is in
'Holiday and Sickness Tracker 11'!$A$1:$A$9916
And I7
And what is "End Of Year" ? - Is this a named range or something?

Are you trying to restrict the Sum based on a Start Date and End Date?

Try

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

 

[Daniellel]

Is there anyway i can attach what i am working on? I am really not sure how to describe what i am doing...

 

[Jonmo1]

Is there anyway i can attach what i am working on? I am really not sure how to describe what i am doing...

See my signature for a method to show us what you're working on...


You can describe what your're doing by telling us

What type of data is in
'Holiday and Sickness Tracker 11'!$A$1:$A$9916
And I7
And what is "End Of Year" ? - Is this a named range or something?

 

[Daniellel]

What type of data is in
'Holiday and Sickness Tracker 11'!$A$1:$A$9916 - This is a tracker of holidays with letters and numbers to log the type, in column A is a row stating the month and then all of the employees working within this month and then the next month etc...
And I7 - This is the log of what month the holiday entitlemnet starts from - they all go to the end of the year. Because this month start can change, i am trying to get it to consider where it needs to start couting from using this cell
And what is "End Of Year" ? - Is this a named range or something? - This is just a cell with "End of year" so i can see where to stop my range at (December)

Does this help???

My complany wont let me upload this

 

[Jonmo1]

OK, so column A is TEXT strings, like January, February etc?
And I7 is also a similar TEXT string?

And somewhere in Column A is a Cell containing the text End Of Year ?

 

[Daniellel]

OK, so column A is TEXT strings, like January, February etc?
And I7 is also a similar TEXT string?

And somewhere in Column A is a Cell containing the text End Of Year ?

Yes to both - But in Column A there are names inbetween each month too, like...

January
Danielle Christou
Adam Smith
Joe Bloggs
February
Danielle Christou
Adam Smith
Joe Bloggs
Amy Score

ETC........


I7 is just the month

 

[Jonmo1]

I would recommend keeping the match formulas seperate...

For example
A1: =MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)
A2: =MATCH("End Of Year",'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)

Then use

=SUMIF(INDEX('Holiday and Sickness Tracker 11'!$A$1:$A$9916,A1):INDEX('Holiday and Sickness Tracker 11'!$A$1:$A$9916,A2),$A7,INDEX('Holiday and Sickness Tracker 11'!$AN$1:$AN$9916,A1):INDEX('Holiday and Sickness Tracker 11'!$AN$1:$AN$9916,A2))