=countif (indirect(Sheet1!...lost after that

[screach]

Wanting to count the occurrences of a word in column A on Sheet1!. I do not have a predefined range of cells in column A as that range will grow over time and I would prefer not to have to rewrite the formula, so I was reading where indirect can allow you to specify a column or row in a formula. I read that the =countif(A1:A5,"DOG") will give me the occurrences of a word, in this case dog, so I am trying to put the two together but am getting a bit lost n the formula.

=countif(indirect(Sheet1!(A,"DOG))) did not work.

Any and all help as always is greatly appreciated.

 

[Hui]

Try:

=Countif(Indirect("Sheet1"&"!"&"A:A"),"Dog")

or

=Countif(Indirect(A1&"!"&B1),"Dog")
where A1 contains "Sheet1" & B1 contains "A:A"

The contents of Indirect has to be a Valid Range Address

 

[screach]

Thanks Hui!