A question about variable in a function

N

Nikos Antoniadis

Good morning,
I was looking to find out if i can insert in the range criteria of a
function a variable.
I will explain what i mean with an example.
Assuming we have the following function =COUNTIF($C$6:$G$50;A2), and i want
the number 50 to be a variable, because i have this function in many cells,
i want to be able to change this value at once, for example taking the data
of another cell and put it in the function. I am open to suggestion if there
is another way doind this.
 
B

Bob Phillips

=COUNTIF(INDIRECT("$C$6:$G$"&A1);A2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jay

Bob said:
=COUNTIF(INDIRECT("$C$6:$G$"&A1);A2)
Click to expand...

Hi Bob,

I see what the above Indirect is doing (concatenating the number in A1
to the $G$ cell ref as the column number) but would you mind explaining
the use of ""? As this appears to be something I could use and I'd like
to understand the rules surrounding the use of inverted commas.

Many thanks,

Jason
 
B

Bob Phillips

That is just quotes, because it is passing a string to the INDIRECT
function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jay

Nikos said:
Good morning,
I was looking to find out if i can insert in the range criteria of a
function a variable.
I will explain what i mean with an example.
Assuming we have the following function =COUNTIF($C$6:$G$50;A2), and i want
the number 50 to be a variable, because i have this function in many cells,
i want to be able to change this value at once, for example taking the data
of another cell and put it in the function. I am open to suggestion if there
is another way doind this.
Click to expand...

Nikos / Bob

Something I don't understand is the semi-colon in the formula:

=COUNTIF($C$6:$G$50;A2)

Is it a typo? I've not come across a semi-colon being used in countif,
only the default format :- COUNTIF(range,criteria).

If it isn't a typo, how does it work? Because I can't get it to work?

Jay
 
R

Ragdyer

Different 'national' versions of XL use different delimiters.
As you've noticed, European versions use the semi-colon " ; " instead of the
comma " , ".
 
J

Jay

Ragdyer said:
Different 'national' versions of XL use different delimiters.
As you've noticed, European versions use the semi-colon " ; " instead of the
comma " , ".
Click to expand...

Ah, that makes quite a few things clearer :)

Many thanks,

-Jay-
 
D

daddylonglegs

I realise I've missed the boat slightly on this one but an alternative
to Bob's Indirect suggestion.....

=COUNTIF($C$6:INDEX($C:$C;A1);A2)
 
Top