Formulaic range with CountIf

[exoticdisease]

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

=countif(address(blahblahblah):address(blahblahblah),"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.

 

[Jacob Skaria]

Use INDIRECT()

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

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


If this post helps click Yes

 

[Stefi]

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:

 

[exoticdisease]

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....

 

[Stefi]

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:

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Dougal Everingham]

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: (e-mail address removed)
w: www.KompasConsulting.com.au and w: www.linkedin.com/in/everingham