COUNTIF using range defined by ADDRESS fuction

flixtan

New Member
Joined
May 31, 2012
Messages
8
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 =)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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!!
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
For the quick answer...

=COUNTIF(INDIRECT(ADDRESS(firstformulayouposted) & ":" & ADDRESS(2ndformulayouposted)),"A")
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,211,686
Messages
6,103,297
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top