Formula that sums/counts across worksheets with variable reference



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?



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question