COUNTIF using range defined by ADDRESS fuction

[flixtan]

Hi all, I'm new here.
Just have a question on how to solve this problem.

So basically I have a table of values, one with dates on the columns and the other with employee number on the rows.

What I want to do is then get a count of "A" in between two dates.
So what I have done is use the ADDRESS function to get the required Range
The problem is now that I can't seem to use the value that was returned by the ADDRESS range into the COUNTIF formula to make up the lookup range.

Does that make sense?

Thanks in advance for the help =)

 

[Jonmo1]

Try this

=SUMPRODUCT(('Mthly Roster (2)'!A3:Z3>=M7)*('Mthly Roster (2)'!A3:Z3<=I9)*(INDEX('Mthly Roster (2)'!A1:Z1000,MATCH('Annual Leave'!B14, 'Mthly Roster (2)'!B:B,0),0)="A"))

Make sure the ranges
A3:Z3 and A1:Z1000
Are sufficiently large to cover the entire dataset
We prefer to not use entire column/row references.
Use defined row#s and column letters.
They should both reference the same columns.

 

[flixtan]

THANK YOU SO MUCH!!! =D ure so awesome!! it works a treat!!!