I'm not sure if this will help, but assuming that your range of
non-contiguous cells are A1, B4:B6, and C7:C12, try...

=SUMPRODUCT(SUMIF(INDIRECT(E1:E3),"<"))

....where E1:E3 contains the following references:

E1: A1
E2: B4:B6
E3: C7:C12

Hope this helps!

In article ,
wrote:

I was trying to use the indirect command to sum a named
range of cells. If I enter the following in a cell then
the formula works if the text in the cell refers to a
contiguous range. If I change the range to be non-
contiguous, it gives a #ref error in the cell.
Cell contents
=+SUM(INDIRECT(I66))
I66 has the string "rng1" which is a named range on the
current worksheet.
Does anyone have any idea of any work around?