countif

[Guest]

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

 

[Guest]

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

 

[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

 

[Guest]

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

 

[JulieD]

Hi reno

you're welcome and thanks for the feedback.

Cheers
JulieD