Thanks Harlan! The formula you provided worked as the original one and
returned values when all cells in the Range 1 (worksheet list) had entries.

When I put it on another worksheet (same format as the original) and had
only one worksheet listed in the cells for Range 1, no values returned. I
got a #REF error (actually displayed as there was no ISERROR arguement).
When I did a formula eval, as i stepped through, it showed #REF errors for
each blank cell.

I did make sure I entered the array with CSE.

Ideas?

Thanks!
--
Greg


"Harlan Grove" wrote:

Greg in CO wrote...
....
The formula is as follows:

=IF(ISERROR(SUMPRODUCT(SUMIF(INDIRECT(Range1),$A1 0,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1)))),
"",SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET( INDIRECT(Range2),,COLUMNS($A:A)-1))))
....
Sooooooooooo....when A5:A9 are all filled with names, the formula works like
a charm (many thanks to the Excel gurus here for helping me learn about these
complex formulae)...but when any of the cells in A5:A9 are blank, the formula
does not return any values. Using the Formula Auditing function and going
through the evaluation, as I step thorugh the formula, I get #REF errors for
the blank cells.
....
Is there way to make this formula work and ignore the blank cells?
....

You have to use an array formula.

=SUM(IF(ISNUMBER(ROWS(INDIRECT(Range1))),
SUMIF(INDIRECT(Range1),
$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1))))