I'm using a countif function and I want to specify the range with a
formula...e.g.

=countif(address(blahblahblah):address(blahblahbla h),"apples")

or whatever it might be...but I just can't seem to get it to work! Is the
address function not the way to go? I managed to get it to say

=countif(A198:a234,"apples")

in the evalutate formula thing, but it appeared to treat that as text, not a
range and just ignored it. Any help much appreciated.

Use INDIRECT()

=COUNTIF(INDIRECT("A" & ":" & "A"),"apples")
OR
=COUNTIF(INDIRECT("A1:" & "A100"),"apples")

=COUNTIF(INDIRECT("address1" & ":" & "address2"),"apples")


If this post helps click Yes
---------------
Jacob Skaria


"exoticdisease" wrote:

I'm using a countif function and I want to specify the range with a
formula...e.g.

=countif(address(blahblahblah):address(blahblahbla h),"apples")

or whatever it might be...but I just can't seem to get it to work! Is the
address function not the way to go? I managed to get it to say

=countif(A198:a234,"apples")

in the evalutate formula thing, but it appeared to treat that as text, not a
range and just ignored it. Any help much appreciated.

If C2="A"
D2=198
E2=234
then
=COUNTIF(INDIRECT(C2&D2):INDIRECT(C2&E2),"apples")
is equivalent with
=countif(A198:a234,"apples")

Regards,
Stefi


€žexoticdisease€� ezt Ã*rta:

I'm using a countif function and I want to specify the range with a
formula...e.g.

=countif(address(blahblahblah):address(blahblahbla h),"apples")

or whatever it might be...but I just can't seem to get it to work! Is the
address function not the way to go? I managed to get it to say

=countif(A198:a234,"apples")

in the evalutate formula thing, but it appeared to treat that as text, not a
range and just ignored it. Any help much appreciated.

Ok, so I wrote the following formula:

=COUNTIF(INDIRECT(ADDRESS(MATCH(A24,A:A,),2,)&":"& ADDRESS(MATCH(A40,A:A,),2,),),"food")

Which, when evaluated, got as far as:

=countif($B$24:$B$40,"food")

but then returned a #REF... :(

It looked promising....

If C2="A",
D2=198,
E2=234,
then
=COUNTIF(INDIRECT(C2&D2):INDIRECT(C2&E2),"apples")
is equivalent with
=countif(A198:a234,"apples")

Regards,
Stefi



€žexoticdisease€� ezt Ã*rta:

I'm using a countif function and I want to specify the range with a
formula...e.g.

=countif(address(blahblahblah):address(blahblahbla h),"apples")

or whatever it might be...but I just can't seem to get it to work! Is the
address function not the way to go? I managed to get it to say

=countif(A198:a234,"apples")

in the evalutate formula thing, but it appeared to treat that as text, not a
range and just ignored it. Any help much appreciated.

Try the below..
=COUNTIF(INDIRECT(ADDRESS(MATCH(A24,A:A,),2,)&":"& ADDRESS(MATCH(A40,A:A,),2,)),"food")

If this post helps click Yes
---------------
Jacob Skaria


"exoticdisease" wrote:

Ok, so I wrote the following formula:

=COUNTIF(INDIRECT(ADDRESS(MATCH(A24,A:A,),2,)&":"& ADDRESS(MATCH(A40,A:A,),2,),),"food")

Which, when evaluated, got as far as:

=countif($B$24:$B$40,"food")

but then returned a #REF... :(

It looked promising....

Hi, If I understand correctly, you want to count the number of times
the word "apples" appears in the range of cells A198 to A234.

The syntax is correct, but if the cell is not preformng the
calculation, check to see what the format is being applied to the
cell. It would be appear to a Text format on the cell as when I
copied the syntax of your formula into excell, it resolved the formula
with no problems including capitilisation of the formula and cell
references. Ensure the format of the cell you are working in is
General and not text


Dougal Everingham
Kompas Consulting

kompas [Danish] (instrument) for determining direction or to give
bearing

m: +61 431 475 606
e:
w: www.KompasConsulting.com.au and w: www.linkedin.com/in/everingham


On Jul 9, 7:39*pm, exoticdisease
wrote:
I'm using a countif function and I want to specify the range with a
formula...e.g.

=countif(address(blahblahblah):address(blahblahbla h),"apples")

or whatever it might be...but I just can't seem to get it to work! *Is the
address function not the way to go? *I managed to get it to say

=countif(A198:a234,"apples")

in the evalutate formula thing, but it appeared to treat that as text, not a
range and just ignored it. *Any help much appreciated.