Try this

=SUMPRODUCT(--(INDIRECT("'Access Data'!B$1:B"&A1)=$A5))

but why don't you just use a dynamic name for John etc.?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Paul Dennis" wrote in message
...
=SUMPRODUCT(--((John)=$A5))
Defined field John = 'Access Data'!B$1:B$65000

This works, however I don't want it to go through 65000 each time, only
the
number of rows that have data, hence tried =SUMPRODUCT(--(('Access
Data'!B$1:aaaa)=$A5)) where aaaa is defined as a cell with the count of
rows,
but Excel returns #Value.

Is there any way to do this.
PS. Need to use SUMPRODUCT as the full statement is =IF($D6<"
",SUMPRODUCT(--(SEVERITY=$A6),--(BREACHYORN="NOBREACH")++(BREACHYORN =
"NOSLA"),--(ROLL_MONTH_NUM = 1))," ")