Array across multiple worksheets

[rda]

I have a situation where I am attempting to summarize how many times a value
in a cell occurs across multiple worksheets within the same range on each.
The problem that I have is that I need to limit the search based on the
current week that I am working with. The worksheets are labeled
"Week1","Week2", etc.

Here is the formula that I am attempting to use which isn't working:

=SUM(COUNTIF(INDIRECT("Week"&ROW(INDIRECT("1:"&B8))&"!B84:B89"),D13))

where the value in B8 is 3 and the value in D13 is 4

Thanks in advance
RDA

 

[Bob Phillips]

What is the issue, it works if you array-enter it.

If you don't want to array-enter, use

=SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIRECT("1:"&B8))&"!B84:B89"),D13))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)