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)

"Tasha" wrote in message
...
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.....