ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   countif (https://www.excelbanter.com/excel-worksheet-functions/5634-countif.html)

reno

countif
 
want to use countif and conditional formatting. can you use a variable in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change with the
variable value w/o re-entering all.
any ideas

JulieD

Hi reno

not sure exactly what you're saying here, if you're using a formula such as
=COUNTIF(A1:A10,50)
then you need to change the 50 to 55 if that's what you now want to count,
alternatively you can reference a cell in the countif function
=COUNTIF(A1:A10,B1)
and then the countif will update if the value in B1 changes as long as the
calculation mode is set to automatic (tools / options / calculation - ensure
automatic is selected).

hope this helps
Cheers
JulieD

"reno" wrote in message
...
want to use countif and conditional formatting. can you use a variable in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change with the
variable value w/o re-entering all.
any ideas




reno

thx, this will work, but wanted to use a named variable

"JulieD" wrote:

Hi reno

not sure exactly what you're saying here, if you're using a formula such as
=COUNTIF(A1:A10,50)
then you need to change the 50 to 55 if that's what you now want to count,
alternatively you can reference a cell in the countif function
=COUNTIF(A1:A10,B1)
and then the countif will update if the value in B1 changes as long as the
calculation mode is set to automatic (tools / options / calculation - ensure
automatic is selected).

hope this helps
Cheers
JulieD

"reno" wrote in message
...
want to use countif and conditional formatting. can you use a variable in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change with the
variable value w/o re-entering all.
any ideas





JulieD

Hi reno

if i use insert / name / define and create a variable called "const" and
assign the number 5 to it (in refers to) then using =COUNIF(A1:A10,const)
returns the number of 5's in the range, if i then change the refers to to
10, it returns the number of 10s in the range ...

what's different for you
Cheers
JulieD

"reno" wrote in message
...
thx, this will work, but wanted to use a named variable

"JulieD" wrote:

Hi reno

not sure exactly what you're saying here, if you're using a formula such
as
=COUNTIF(A1:A10,50)
then you need to change the 50 to 55 if that's what you now want to
count,
alternatively you can reference a cell in the countif function
=COUNTIF(A1:A10,B1)
and then the countif will update if the value in B1 changes as long as
the
calculation mode is set to automatic (tools / options / calculation -
ensure
automatic is selected).

hope this helps
Cheers
JulieD

"reno" wrote in message
...
want to use countif and conditional formatting. can you use a variable
in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change with
the
variable value w/o re-entering all.
any ideas







reno

JuileD, thanks, i must have used a reserved word as the variable name, this
seems to work fine now, thought it would.
thx

"JulieD" wrote:

Hi reno

if i use insert / name / define and create a variable called "const" and
assign the number 5 to it (in refers to) then using =COUNIF(A1:A10,const)
returns the number of 5's in the range, if i then change the refers to to
10, it returns the number of 10s in the range ...

what's different for you
Cheers
JulieD

"reno" wrote in message
...
thx, this will work, but wanted to use a named variable

"JulieD" wrote:

Hi reno

not sure exactly what you're saying here, if you're using a formula such
as
=COUNTIF(A1:A10,50)
then you need to change the 50 to 55 if that's what you now want to
count,
alternatively you can reference a cell in the countif function
=COUNTIF(A1:A10,B1)
and then the countif will update if the value in B1 changes as long as
the
calculation mode is set to automatic (tools / options / calculation -
ensure
automatic is selected).

hope this helps
Cheers
JulieD

"reno" wrote in message
...
want to use countif and conditional formatting. can you use a variable
in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change with
the
variable value w/o re-entering all.
any ideas







JulieD

Hi reno

you're welcome and thanks for the feedback.

Cheers
JulieD

"reno" wrote in message
...
JuileD, thanks, i must have used a reserved word as the variable name,
this
seems to work fine now, thought it would.
thx

"JulieD" wrote:

Hi reno

if i use insert / name / define and create a variable called "const" and
assign the number 5 to it (in refers to) then using =COUNIF(A1:A10,const)
returns the number of 5's in the range, if i then change the refers to to
10, it returns the number of 10s in the range ...

what's different for you
Cheers
JulieD

"reno" wrote in message
...
thx, this will work, but wanted to use a named variable

"JulieD" wrote:

Hi reno

not sure exactly what you're saying here, if you're using a formula
such
as
=COUNTIF(A1:A10,50)
then you need to change the 50 to 55 if that's what you now want to
count,
alternatively you can reference a cell in the countif function
=COUNTIF(A1:A10,B1)
and then the countif will update if the value in B1 changes as long as
the
calculation mode is set to automatic (tools / options / calculation -
ensure
automatic is selected).

hope this helps
Cheers
JulieD

"reno" wrote in message
...
want to use countif and conditional formatting. can you use a
variable
in
countif?
e.g. say break point is $x.xx and i change, i want the conditional
formatting to change, no problem, but the countif does not change
with
the
variable value w/o re-entering all.
any ideas










All times are GMT +1. The time now is 06:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com