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)&":P2",$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)&":P2" = "F2:P2" Which is the problem
Because it's giving me "F2:P2" (with the quotes) instead of just F2:P2. 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)):P2,$C$20)

Biff

"dark" <[email protected]> wrote in message
news:[email protected]...
>
> 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)&":P2",$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)&":P2" = "F2:P2" Which is the problem
> Because it's giving me "F2:P2" (with the quotes) instead of just F2:P2.
> 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.
>
>
> --
> dark
> ------------------------------------------------------------------------
> dark's Profile:
> http://www.excelforum.com/member.php...o&userid=27723
> View this thread: http://www.excelforum.com/showthread...hreadid=472378
>

[Biff]

Another way:

=COUNTIF(OFFSET(A2,,C35-1):P2,C20)

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> =COUNTIF(INDIRECT(ADDRESS(2,C35)):P2,$C$20)
>
> Biff
>
> "dark" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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)&":P2",$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)&":P2" = "F2:P2" Which is the problem
>> Because it's giving me "F2:P2" (with the quotes) instead of just F2:P2.
>> 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.
>>
>>
>> --
>> dark
>> ------------------------------------------------------------------------
>> dark's Profile:
>> http://www.excelforum.com/member.php...o&userid=27723
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=472378
>>
>
>

[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! :P
=COUNTIF(INDIRECT(ADDRESS(2,MATCH(NOW()+CHOOSE(WEEKDAY(NOW()),5,4,3,2,1,7,6),$1:$1))):IV2,$B$20)*3