Calculating the range in a countif formula

[BooBoo]

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!

 

[Pete_UK]

Try putting your range calculation within INDIRECT( ), as you have for
the first formula.

Hope this helps.

Pete

 

[BooBoo]

Well, I finally put it all together. Thanks to Pete. The formula
that I can use, which is not dependent on hardcoded cell references is
as follows:

=COUNTIF(INDIRECT(CONCATENATE("TimeSheet!", ADDRESS(INT((ROW() -3) /
9) * 25 + 4,(MOD(ROW(), 9) - 2) * 3), ":", ADDRESS(INT((ROW() -3) / 9)
* 25 + 23,(MOD(ROW(), 9) - 2) * 3))),
CONCATENATE(OFFSET(INDIRECT(ADDRESS(ROW(), 1)), (MOD(ROW(), 9) - 2) *
-1, COLUMN() - 1), "*")) * 0.5

I can copy this into any cell within a section and it finds the proper
range on a different worksheet, counts the half hour increments by
category, and it is not dependent on any absolute or relative cell
addressing. All it needs to know is where the data is on the other
worksheet and that is determined by the row and column that the
formula is in on the breakdown worksheet.

Thanks for all of your help!

 

[Pete_UK]

Thanks for feeding back - glad you got it to work in the end.

Pete