Named Range in SUMPRODUCT

  • Thread starter Thread starter Ed
  • Start date Start date
E

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.
 
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
Click to expand...

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.
 
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.



Harlan Grove said:
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
Click to expand...

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.
Click to expand...
 
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.
Click to expand...

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)
 
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




Harlan Grove said:
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
Click to expand...
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.
Click to expand...
 
Back
Top