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
on it for now and make sure it is totalling correctly, but so far, so good!!!
THANKS AGAIN!!! so much for your help!!!

"Harlan Grove" wrote:

"Bob Phillips" wrote...
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.