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" wrote:

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