=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))," ")