Formula that sums/counts across worksheets with variable reference

[andy62]

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

 

[Domenic]

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