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]

The quick and easy (but not necessarily best) solution is to use the Indirect function.

=COUNTIF(INDIRECT(ADDRESS(...,...)),"A")

However, if you could post the ADDRESS formula that achieves the desired range, there is likely a "better" way to do it.

Indirect is one of the functions we like to avoid as much as possible.
It's a volatile function, which means it recalculates every time anything changes,
regardless if what changed was related to that indirect funciton or not.

This is not terrible if it's used only a few times.
But if you start getting into hundreds of formulas using it, then you'll really start to notice the performance of the book drop.

 

[flixtan]

Hi thanks for the quick reply! =)

The formula that I used is as below
ADDRESS(MATCH('Annual Leave'!B14, 'Mthly Roster (2)'!B:B,0),MATCH(M7, 'Mthly Roster (2)'!3:3,0), 4) this returns P6

ADDRESS(MATCH('Annual Leave'!B14, 'Mthly Roster (2)'!B:B,0),MATCH(I9, 'Mthly Roster (2)'!3:3,0), 4) this returns EE6

So i want to use both of that as my range lookup, countif(P6:EE6, "A") essentially what I'm trying to achieve, but i need it to be automatic.
but when I try to put the ADDRESS formula into the COUNTIF, it just say formula error.

What does an INDIRECT formula do? I'm not very familiar with it unfortunately.

Thanks again!!

 

[Jonmo1]

The indirect function allows you to build a Text string that looks like a range refernce.
Then uses that text string as a range reference.

Example..
In A1, put Z1

Then use a formula
=INDIRECT(A1)
This returns the value that is in Z1


Now, on to the "better way"

What type of values are in Row 3 of the mnthly roster sheet?
Are they numeric or text? Or could be either?
Are they sorted in any way, ascending or descening ?
And what is in M7 and I9 ?

 

[flixtan]

Ah excellent!!
Row 3 of the Mthly Roster is a "date" value. It is in chronological order
M7 contains the date and I9 is the other date.
I want to calculate the "A"s in between those 2 dates.

 

[Jonmo1]

For the quick answer...

=COUNTIF(INDIRECT(ADDRESS(firstformulayouposted) & ":" & ADDRESS(2ndformulayouposted)),"A")

 

[Jonmo1]

Ah excellent!!
Row 3 of the Mthly Roster is a "date" value. It is in chronological order
M7 contains the date and I9 is the other date.
I want to calculate the "A"s in between those 2 dates.

Excellet...This will be MUCH better than indirect with address...

So row 3 is dates increasing from left to right, correct?

Which date is Earlier M7 or I9 ?

 

[flixtan]

M7 is earlier. I9 is the later. Cheers!

 

[Jonmo1]

And which version of Excel are you using?

 

[flixtan]

Excel 2003....unfortunately... ><