Yay! I entered the formula you updated...Excel made a correction (a missing
parenthese, I beleive) and the formula works. I went through the
Evaluator...as I got the end where it shows the values avaialble after doing
to look up and comapre, it showed 100, False, False, False, False for the
final range, which is correct, as there was only one worksheet name entry in
the worksheet name range. During the eval, I was still getting the #VALUE,
#REF sequence, but the current structure ignores the #REF errors and returns
the one value it finds: 100.

Thanks!

You get today's "You rock!" award...feel free to tell yourself you rock! ;)

Greg
--
Greg


"Harlan Grove" wrote:

Greg in CO wrote...
I enterd the reformatted formula (using CSE) and dtill got #REF errors. It
does not like the initial cell range in each of the named ranges:
....

Looks like I didn't test thoroughly. Try the following instead.

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