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

Well,

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

seems to work, but it's a terrible kludge. Surely there is a more
"elegant" formula?

Fred Holmes


On Wed, 17 Jun 2009 17:30:22 -0400, 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

You could use two =sumif()'s. RC[-3]:RC[-1] is a single area.



Fred Holmes wrote:

Well,

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

seems to work, but it's a terrible kludge. Surely there is a more
"elegant" formula?

Fred Holmes

On Wed, 17 Jun 2009 17:30:22 -0400, 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

--

Dave Peterson

Try this:

=SUM(SUMIF(INDIRECT({"RC[-1]","RC[-2]","RC[-3]","RC[-9]"},0),"<#NAME?"))

--
Biff
Microsoft Excel MVP


"Fred Holmes" wrote in message
...
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

But, while the provided formula "works," it does not "adjust" if a
column is inserted/deleted in the worksheet. I need a formula that
adapts to column insertions and deletions "in the usual fashion."

The "fixed" reference is a property of the INDIRECT() function.

Fred Holmes


On Wed, 17 Jun 2009 21:54:37 -0400, "T. Valko"
wrote:

Try this:

=SUM(SUMIF(INDIRECT({"RC[-1]","RC[-2]","RC[-3]","RC[-9]"},0),"<#NAME?"))

--
Biff
Microsoft Excel MVP


"Fred Holmes" wrote in message
.. .
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

Then you'll have to use the kludge method:

=SUMIF(...)+SUMIF(...)+etc

--
Biff
Microsoft Excel MVP


"Fred Holmes" wrote in message
...
But, while the provided formula "works," it does not "adjust" if a
column is inserted/deleted in the worksheet. I need a formula that
adapts to column insertions and deletions "in the usual fashion."

The "fixed" reference is a property of the INDIRECT() function.

Fred Holmes


On Wed, 17 Jun 2009 21:54:37 -0400, "T. Valko"
wrote:

Try this:

=SUM(SUMIF(INDIRECT({"RC[-1]","RC[-2]","RC[-3]","RC[-9]"},0),"<#NAME?"))

--
Biff
Microsoft Excel MVP


"Fred Holmes" wrote in message
. ..
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

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

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

The way i see it the second one uses the same references as the sum formula
given in the original post so should work in the same way.

I may well have misinterpreted this however, and sumif+sumif+.. is
the easier method. These just show a different technique.

"T. Valko" wrote:

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

Thanks to everyone for all your suggestions.

Fred Holmes