Variable in Cell Reference

  • Thread starter Thread starter Bill Astarita
  • Start date Start date
B

Bill Astarita

I want to do a CountIF function on a range of cells. The range will change
daily. For example - Today is the 2nd so I want the Range to be A1:B2,
tommorrow the 3rd I want the Range to be A1:B3. I know how to automatically
make it update each day, but how do I use a function or variable (name) to
reference a row or column? I was thinking something like this:

CountIF(A1:"B" & "Day(Today()), criteria).

Thanks!
 
Hi,
Use Indirect Function.

=COUNTIF(INDIRECT("A1:B"&DAY(TODAY())), criteria)

--
Regards

Haldun Alay

To e-mail me, please replace AT and DOT in my e-mail address with the original signs.



"Bill Astarita" <[email protected]>, iletide þunu yazdý I want to do a CountIF function on a range of cells. The range will change
daily. For example - Today is the 2nd so I want the Range to be A1:B2,
tommorrow the 3rd I want the Range to be A1:B3. I know how to automatically
make it update each day, but how do I use a function or variable (name) to
reference a row or column? I was thinking something like this:

CountIF(A1:"B" & "Day(Today()), criteria).

Thanks!
 
Why not make your range dynamic
insert>name>define>type in some name like myrng>in the refers to
=offset($a$1,0,0,counta($a:$a),2)
Now it will self adjust if there are no blanks.
=countif(myrng,criteria)
 
Back
Top