Named Range in SUMPRODUCT

[Ed]

This is my current formula.

=SUMPRODUCT(('[Test Data.xls]Phoenix - Data'!$H$2:$H$959=A10)*('[Test
Data.xls]Phoenix - Data'!$G$2:$G$959="ACC"),'[Test Data.xls]Phoenix -
Data'!$F$2:$F$959)


On the sheet named Phoenix - Data I defined a named range for column H. I
named it GRP_PHX and it refers to (='Phoenix - Data'!$H:$H). My question is
in my current formula how can I refer to the named range. I want to replace
this part of it [Test Data.xls]Phoenix - Data'!$H$2:$H$959

T.I.A.

 

[Harlan Grove]

This is my current formula.

=SUMPRODUCT(('[Test Data.xls]Phoenix - Data'!$H$2:$H$959=A10)*('[Test
Data.xls]Phoenix - Data'!$G$2:$G$959="ACC"),'[Test Data.xls]Phoenix -
Data'!$F$2:$F$959)


On the sheet named Phoenix - Data I defined a named range for column H. I
named it GRP_PHX and it refers to (='Phoenix - Data'!$H:$H). My question is
in my current formula how can I refer to the named range. I want to replace
this part of it [Test Data.xls]Phoenix - Data'!$H$2:$H$959

If GRP_PHX refers to the entire column H, which seems to be the case, you'd need
to restrict yourself to rows 2 through 959, so you could use

=SUMPRODUCT((OFFSET('[Test Data.xls]GRP_PHX',1,0,958,1)=A10)
*('[Test Data.xls]Phoenix - Data'!$G$2:$G$959="ACC"),
'[Test Data.xls]Phoenix - Data'!$F$2:$F$959)

but this won't work if you close the Test Data.xls file because OFFSET's 1st
argument must be a reference to a range in an *open* workbook. On the other
hand, if your goal were shortening the formula, this doesn't help you.

 

[Ed]

Restricting myself to rows 2 through 959 is what I am trying to avoid. That
was the extent of my data the first time, but each time time I run it, it
will be different. My thinking was I would insert new data into the names
range everytime I run this. Then paste values before I close Test Data.xls.

This is my current formula.

=SUMPRODUCT(('[Test Data.xls]Phoenix - Data'!$H$2:$H$959=A10)*('[Test
Data.xls]Phoenix - Data'!$G$2:$G$959="ACC"),'[Test Data.xls]Phoenix -
Data'!$F$2:$F$959)


On the sheet named Phoenix - Data I defined a named range for column H. I
named it GRP_PHX and it refers to (='Phoenix - Data'!$H:$H). My question is
in my current formula how can I refer to the named range. I want to replace
this part of it [Test Data.xls]Phoenix - Data'!$H$2:$H$959

If GRP_PHX refers to the entire column H, which seems to be the case, you'd need
to restrict yourself to rows 2 through 959, so you could use

=SUMPRODUCT((OFFSET('[Test Data.xls]GRP_PHX',1,0,958,1)=A10)
*('[Test Data.xls]Phoenix - Data'!$G$2:$G$959="ACC"),
'[Test Data.xls]Phoenix - Data'!$F$2:$F$959)

but this won't work if you close the Test Data.xls file because OFFSET's 1st
argument must be a reference to a range in an *open* workbook. On the other
hand, if your goal were shortening the formula, this doesn't help you.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

 

[Harlan Grove]

Restricting myself to rows 2 through 959 is what I am trying to avoid. That
was the extent of my data the first time, but each time time I run it, it
will be different. My thinking was I would insert new data into the names
range everytime I run this. Then paste values before I close Test Data.xls.

If you want GRP_PHX to span all of column H, then the other two columns in the
same workbook would also need to span all of their respective columns.
SUMPRODUCT *requires* that all of its arguments have *exactly* the same size.

That said, SUMPRODUCT (and most other worksheet functions) won't work with
entire column ranges. This is one of Excel's known if not transparently
documented limitations. If you're never going to perform calculations on row 1,
then you should at least define GRP_PHX as 'Phoenix - Data'!$H$2:$H$65536. But
you'd then need to use a formula like

=SUMPRODUCT(('[Test Data.xls]GRP_PHX'=A10)
*('[Test Data.xls]Phoenix - Data'!$G$2:$G$65536="ACC"),
'[Test Data.xls]Phoenix - Data'!$F$2:$F$65536)

 

[Ed]

Thanks Harlan for the info.
I was planing on creating named ranges for the other two columns, but I
might as well use the entire range $H$2:$H$65536 and
same for the other columns. There seems to be no advantage for using the
names ranges. You have given me some options and some great insight.

Thanks
Ed

Restricting myself to rows 2 through 959 is what I am trying to avoid. That
was the extent of my data the first time, but each time time I run it, it
will be different. My thinking was I would insert new data into the names
range everytime I run this. Then paste values before I close Test

Data.xls.

If you want GRP_PHX to span all of column H, then the other two columns in the
same workbook would also need to span all of their respective columns.
SUMPRODUCT *requires* that all of its arguments have *exactly* the same size.

That said, SUMPRODUCT (and most other worksheet functions) won't work with
entire column ranges. This is one of Excel's known if not transparently
documented limitations. If you're never going to perform calculations on row 1,
then you should at least define GRP_PHX as 'Phoenix - Data'!$H$2:$H$65536. But
you'd then need to use a formula like

=SUMPRODUCT(('[Test Data.xls]GRP_PHX'=A10)
*('[Test Data.xls]Phoenix - Data'!$G$2:$G$65536="ACC"),
'[Test Data.xls]Phoenix - Data'!$F$2:$F$65536)

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.