You must have missed the OP's follow-up:

I need a formula that adapts to column insertions and deletions

So anything that uses array constants is out.

--
Biff
Microsoft Excel MVP


"Lori" wrote in message
...
If you can replace the error #name? with text "name?" then your normal
sum formula should work fine.

If not maybe one of these CTRL+SHIFT+ENTERED in xl2007:

=SUM(IFERROR(IF({1;1;1;0},RC[-3]:RC[-1],RC[-9]),""))
=SUM(IFERROR(CHOOSE({1;2;3;4},RC[-3],RC[-2],RC[-1],RC[-9]),""))

For other versions use IF(ISERR(...),"",...) in place of IFERROR(...,"")

"Fred Holmes" wrote:

Excel 2000 R1C1 reference notation

What is the syntax for the SUMIF function if the range to be summed is
a collection of indivicual cells - separated by commas? The following
function fails:

=SUMIF(RC[-1],RC[-2],RC[-3],RC[-9],"<#NAME?")

If the range were RC[-1]:RC[-9] it would work fine.

=SUM(RC[-1],RC[-2],RC[-3],RC[-9])

Works fine. I'm trying to add the IF <#NAME? to it.

Thanks,

Fred Holmes