An attempt at this earlier today has gone cold.

I need a formula that can sum or count numerical data across four
worksheets, when the cell reference itself varies. The four worksheets are
Rater1, Rater2, Rater3, and Rater4. The cell I need to reference is in a
particular column, but the row number varies. Here's as close as I've gotten
(but it's not working):

{=COUNT(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3))}

The INDIRECT function is to piece together the referenced range. The four
worksheets are "Rater" plus the array 1:4. The cell reference is column G
plus whatever number the user enters into cell B3. If not for this last
complication I could just use 'Rater1:Rater4!'G7, but the varying cell
reference makes it way more complicated. On top of everything, I need to be
sure that the formula counts a zero but does not count a blank as zero. The
data in the cells is restricted to: 1, 0, . (period), blank. What I need
this function to do is tell me if the cells contain all the same nuerical
values, either all 1's or all 0's. Any ideas?

TIA

The following formula will return TRUE if all four cells contain a
zero...

=SUMPRODUCT(COUNTIF(INDIRECT("'Rater"&{1,2,3,4}&"' !G"&$B$3),0))=4

Hope this helps!

http://www.xl-central.com

In article ,
andy62 wrote:

An attempt at this earlier today has gone cold.

I need a formula that can sum or count numerical data across four
worksheets, when the cell reference itself varies. The four worksheets are
Rater1, Rater2, Rater3, and Rater4. The cell I need to reference is in a
particular column, but the row number varies. Here's as close as I've gotten
(but it's not working):

{=COUNT(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3))}

The INDIRECT function is to piece together the referenced range. The four
worksheets are "Rater" plus the array 1:4. The cell reference is column G
plus whatever number the user enters into cell B3. If not for this last
complication I could just use 'Rater1:Rater4!'G7, but the varying cell
reference makes it way more complicated. On top of everything, I need to be
sure that the formula counts a zero but does not count a blank as zero. The
data in the cells is restricted to: 1, 0, . (period), blank. What I need
this function to do is tell me if the cells contain all the same nuerical
values, either all 1's or all 0's. Any ideas?

TIA