SUMIF on a collection of individual cells

[Fred Holmes]

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

 

[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

 

[T. Valko]

Try this:

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

 

[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

Try this:

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

--
Biff
Microsoft Excel MVP

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

 

[T. Valko]

Then you'll have to use the kludge method:

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

--
Biff
Microsoft Excel MVP

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

Try this:

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

--
Biff
Microsoft Excel MVP

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]

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

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

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

 

[Lori]

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(...,"")

 

[T. Valko]

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

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(...,"")

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

 

[Lori]

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.

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

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(...,"")

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

 

[Fred Holmes]

Thanks to everyone for all your suggestions.

Fred Holmes