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)

Whe
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

Hi!

Try this:

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

Biff

"dark" wrote in message
...

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)

Whe
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

Another way:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"dark" wrote in message
...

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)

Whe
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

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(WEE KDAY(NOW()),5,4,3,2,1,7,6),$1:$1))):IV2,$B$20)*3


--
dark
------------------------------------------------------------------------
dark's Profile: http://www.excelforum.com/member.php...o&userid=27723
View this thread: http://www.excelforum.com/showthread...hreadid=472378