Formulaic range with CountIf

  • Thread starter Thread starter exoticdisease
  • Start date Start date
E

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.
 
Use INDIRECT()

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

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


If this post helps click Yes
 
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:
 
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:
 
Try the below..
=COUNTIF(INDIRECT(ADDRESS(MATCH(A24,A:A,),2,)&":"&ADDRESS(MATCH(A40,A:A,),2,)),"food")

If this post helps click Yes
 
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
 
Back
Top