Calculating the range in a countif formula

Giganews Newsgroups
Subject: Calculating the range in a countif formula
Posted by:  BooBoo (KeithOwenRober…@gmail.com)
Date: 29 Jan 2007

I am trying to create a formula that in which I calculate the range in
the countif formula.  I have been able to use the following to
retrieve a value from one worksheet in a different worksheet:

=Indirect(ADDRESS(INT((ROW() -3) / 9) * 25 +
4,COLUMN()-17,1,TRUE,"TimeSheet"))

What I what to do, is to count the occurance of a value in a limited
range in a different worksheet.

I have been able to use this formula:

COUNTIF(TimeSheet!$C$79:$C$98,CONCATENATE(B$29, "*")) *0.5

I would like to be able to calculate the range section based on the
first formula, which is the row and column.  However, I have not been
able to figure this out.  I have tried using a concatenate statement,
but that did not work.  I have tried using =COuntIf(ADDRESS(INT((ROW()
-3) / 9) * 25 + 4,COLUMN()-17,1,TRUE,"TimeSheet"):ADDRESS(INT((ROW()
-3) / 9) * 25 + 23,COLUMN()-17,1,TRUE,"TimeSheet"), CONCATENATE(B$29,
"*")) *0.5, but this did not work either.

I need to use a formula as my company has locked down the ability for
a user to use add-ins or vba.

Is this possible or just a wild goose chase?

Thanks!

Replies