CountIF and changing ranges

[dark]

Hi all,

I'm working on a function that tells me how many cells there are with
"Paid" in it. The problem being, they the range will change depending
on what the date is:

I reckon i'm pretty close with this:

=COUNTIF(ADDRESS(2,C35,4)&"2",$C$20)

Where:
C20 = Paid
C35 = 6 (Don't worry about why this is 6 - Long story, but it changes
depending on the current date)

If I use the forumla bar in excel it tells me that:
ADDRESS(2,C35,4)&"2" = "F22" Which is the problem
Because it's giving me "F22" (with the quotes) instead of just F22.
I'm 99% sure this is causing it to error and not give me a result -
because it's seeing the range as text. I don't know what else to do,
can anyone suggest anything?

Thanks in advance.

 

[Biff]

Hi!

Try this:

=COUNTIF(INDIRECT(ADDRESS(2,C35))2,$C$20)

Biff

 

[Biff]

Another way:

=COUNTIF(OFFSET(A2,,C35-1)2,C20)

Biff

 

[dark]

Ah thanks heaps!
I don't understand the second way, but I do the first. That makes
sense, I thought it would be something that simple. Thanks mate.

Now my formula is complete!
=COUNTIF(INDIRECT(ADDRESS(2,MATCH(NOW()+CHOOSE(WEEKDAY(NOW()),5,4,3,2,1,7,6),$1:$1))):IV2,$B$20)*3