- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel
- 2007 January
- Calculating the range in a countif formula

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!

- Re: Calculating the range in a countif formula posted by Pete_UK on 29 Jan 2007