Need help please-SUMPRODUCT and Dynamic Range

[Tasha]

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and am
to the point of beating my head against the wall.....

 

[Toppers]

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH

 

[Tasha]

I will try that, thanks....and will let you know if it works. Can you please
explain to me the steps in the formula? I have looked in Help, I have also
read on the internet, but thinking I am not understanding the 'Offset' part
of the columns and rows??

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and am
to the point of beating my head against the wall.....

 

[Peo Sjoblom]

Note that offset does not work if the workbook is closed


--
Regards,

Peo Sjoblom

I will try that, thanks....and will let you know if it works. Can you
please
explain to me the steps in the formula? I have looked in Help, I have
also
read on the internet, but thinking I am not understanding the 'Offset'
part
of the columns and rows??

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns
wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has
columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have
been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column
B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the
date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has
named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I
named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm
not
referencing the named ranges right, or if I didn't name them right, or
if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1
2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this
and am
to the point of beating my head against the wall.....

 

[Bob Phillips]

Change the name definitions to

ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1)
PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1)

and add another

PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1)

then use a formula in D2 of

=SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT)

and copy across and down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Toppers]

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

The reference cell from which your offsetting is $D$2. If you are only
interested in a single column , in this case D, then the next two paramters
are set to 0 i.e. 0 rows and 0 columns from Dd2 (D2!).

The COUNTA is the number of rows (height) of the column FROM the offset and
if you start from D2 you should subtract 1 from this, as it will include the
header. The 1 is width (number of columns) so ! in this case.

=OFFSET(dlywrksht!$D$1,0,0,COUNTA(dly wrksht!$D:$D),1)

OR

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D)-1,1)

HTH

I will try that, thanks....and will let you know if it works. Can you please
explain to me the steps in the formula? I have looked in Help, I have also
read on the internet, but thinking I am not understanding the 'Offset' part
of the columns and rows??

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and am
to the point of beating my head against the wall.....

 

[Tasha]

even if the named ranges specify the path to SheetA? When I close the
workbook that has SheetA in it, in SheetB, in my calculated fields, the path
shows up instead of just the worksheet name???

Note that offset does not work if the workbook is closed


--
Regards,

Peo Sjoblom

I will try that, thanks....and will let you know if it works. Can you
please
explain to me the steps in the formula? I have looked in Help, I have
also
read on the internet, but thinking I am not understanding the 'Offset'
part
of the columns and rows??

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns
wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH


:

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has
columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have
been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column
B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the
date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has
named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I
named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm
not
referencing the named ranges right, or if I didn't name them right, or
if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1
2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this
and am
to the point of beating my head against the wall.....

 

[Tasha]

this is my updated formula, still not pulling anything, getting 0 in all
results??? I guess that is better than an error, but not much

=SUMPRODUCT('SheetA.xls'!PHYNO2=B$3)*(SheetA.xls'!ADMDAY2=$D$2)

my named ranges are working though, when I do F5 and type in the name, it
selects the column or cells I named. Then when I save SheetA.xls and close
it, in SheetB, I get an error that the named ranges don't exists , even when
I try to Update the Values in the link.

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

The reference cell from which your offsetting is $D$2. If you are only
interested in a single column , in this case D, then the next two paramters
are set to 0 i.e. 0 rows and 0 columns from Dd2 (D2!).

The COUNTA is the number of rows (height) of the column FROM the offset and
if you start from D2 you should subtract 1 from this, as it will include the
header. The 1 is width (number of columns) so ! in this case.

=OFFSET(dlywrksht!$D$1,0,0,COUNTA(dly wrksht!$D:$D),1)

OR

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D)-1,1)

HTH

I will try that, thanks....and will let you know if it works. Can you please
explain to me the steps in the formula? I have looked in Help, I have also
read on the internet, but thinking I am not understanding the 'Offset' part
of the columns and rows??

Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide

=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

Perhaps ....if it is for column D

=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)

Check ADMDAY as well

=OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B),1)

Perhaps ....if it is for column B


=OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B),1)

HTH


:

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO) and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and am
to the point of beating my head against the wall.....

 

[Harlan Grove]

Change the name definitions to

ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1)
PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1)

....

But the 'dly wrksht' worksheet is in a different, apparently closed
workbook. Excel returns #REF! for names defined as formulas in closed
workbooks.

Note the OP's specs: "this is the formula for each name...
ADMDAY=OFFSET('dly wrksht'!$B$2,1,1,COUNTA('dly wrksht'!$B:$B),1) and
PHYNO=OFFSET('dly wrksht'!$D$2,1,3,COUNTA('dly wrksht'!$D:$D),4)"
[single quotes added]

There's no robust way to do this unless the two COUNTA calls return the same
result. Otherwise, the two derived ranges could have different numbers of
rows, and the different 5th args to OFFSET ensure they have different
numbers of columns. I'll assume the two COUNTA calls return the same result.

In the workbook that needs to refer to this other workbook define the names

ADMDAY:
='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$C$3:$C$1002

PHYNO:
='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$G$3:$J$1002

N:
=COUNTA('H:\ExcelDocs\[SheetA.xls]dly wrksht'!$B:$B)

S:
=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1000,1))

and use a formula like

=SUMPRODUCT((PHYNO=B3)*(ADMDAY=D2)*(S<=N))

Adjust the bottom row index as needed, but don't use more rows than needed.
References to large ranges in other, closed workbooks can really slow things
down and eat memory.

 

[Tasha]

Thank you for all your help Bob!!! I ended up using the SUMPRODUCT formula
you gave me on the 16th with the specific cell references, and then I edited
the range names by inserting $B:B$ and the same for D. Seems to be working,
am going to keep an eye on it for now and make sure it is totalling
correctly, but so far, so good!!! THANKS AGAIN!!! so much for your help!!!

Change the name definitions to

ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1)
PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1)

and add another

PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1)

then use a formula in D2 of

=SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT)

and copy across and down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has
columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO)
and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has
named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I
named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if
my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and
am
to the point of beating my head against the wall.....

 

[Tasha]

Thank you for all your help Bob!!! I ended up using the SUMPRODUCT formula
you gave me on the 16th with the specific cell references, and then I edited
the range names by inserting $B:B$ and the same for D. Seems to be working,
am going to keep an eye on it for now and make sure it is totalling
correctly, but so far, so good!!! THANKS AGAIN!!! so much for your help!!!

Change the name definitions to

ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1)
PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1)

and add another

PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1)

then use a formula in D2 of

=SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT)

and copy across and down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has
columns
B(DAY-which is the day of the month the pt was admitted), and column
D(PHYNO-physicians number). This sheet is updated daily, and I have been
using SUMPRODUCT in SheetB to give me a count of patients admitted by
physician for each day of the month. SheetB is set up as column B(PHYNO)
and
column D(formula shown below). In cell D2 across through AH2 is the date
shown as 1,2,3,4 according to what day of the month it is. Sheet A has
named
ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I
named
these correctly, this is the formula for each name...ADMDAY=OFFSET(dly
wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly
wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4)

=SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly
wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2))

This is not giving me anything but #REF! errors. I don't know if I'm not
referencing the named ranges right, or if I didn't name them right, or if
my
SUMPRODUCT calculation is wrong?????

SheetA

A B C D E
PATNO DAY DAYS PHYNO HSV
201 1 4 0854 M
202 2 2 10440 S
203 3 1 9996 D

SheetB

A B C
D E F G H I J (ETC.)
PHYSICIAN NAME PHYNO SPECIALTY 1 2
3 4 5 6 7
DR FEELGOOD 10440 FP
(FORMULA IN EACH CELL)

Can someone please help me? I've worked and worked for hours on this and
am
to the point of beating my head against the wall.....

 

[Tasha]

I ended up using the SUMPRODUCT formula Bob gave me on the 16th with the
specific cell references, and then I edited the range names by inserting
$B:B$ and the same for D. Oh, and I also copied the spreadsheet I was using
seperately, in with the workbook my summary sheet was on, that way it would
be open when I was updating it. Seems to be working, am going to keep an eye

Change the name definitions to

ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1)
PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1)

....

But the 'dly wrksht' worksheet is in a different, apparently closed
workbook. Excel returns #REF! for names defined as formulas in closed
workbooks.

Note the OP's specs: "this is the formula for each name...
ADMDAY=OFFSET('dly wrksht'!$B$2,1,1,COUNTA('dly wrksht'!$B:$B),1) and
PHYNO=OFFSET('dly wrksht'!$D$2,1,3,COUNTA('dly wrksht'!$D:$D),4)"
[single quotes added]

There's no robust way to do this unless the two COUNTA calls return the same
result. Otherwise, the two derived ranges could have different numbers of
rows, and the different 5th args to OFFSET ensure they have different
numbers of columns. I'll assume the two COUNTA calls return the same result.

In the workbook that needs to refer to this other workbook define the names

ADMDAY:
='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$C$3:$C$1002

PHYNO:
='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$G$3:$J$1002

N:
=COUNTA('H:\ExcelDocs\[SheetA.xls]dly wrksht'!$B:$B)

S:
=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1000,1))

and use a formula like

=SUMPRODUCT((PHYNO=B3)*(ADMDAY=D2)*(S<=N))

Adjust the bottom row index as needed, but don't use more rows than needed.
References to large ranges in other, closed workbooks can really slow things
down and eat memory.